Question about PARTIAL DATE type/s

On Sun, Oct 7, 2012 at 12:28 PM, r d <rd0002@gmail.com> wrote:

Hi,

I have text fields which contains dates in the format 'YYYYMM' (four
positions for the year, two for the month).
These fields are contained in text files which I load into the DB.

When I convert this field to a date using

to_date("PARTIAL_DATE_FIELD",'YYYYMM'); -- (a cast won't recognize
the input as valid)

I still get full dates as an output, for example,
'198801' ---> 1988-01-01
'196408' ---> 1964-08-01
and so on, what is wrong in this case because nowhere it is said that I have
the /first/ of that month, the entry just says that the event in question
happened /during/ that month.

My question:
Is it possible to define fields which contain partial dates per above? I
found nothing in the manual.

There is no "partial date" type. You can use a dates range to
represent explicitly what you mean.

http://www.postgresql.org/docs/9.2/static/rangetypes.html

e.g. this function returns the range requested:

postgres=# create function partial_month(s text)
returns daterange
language sql
as $$
select daterange(
to_date($1, 'YYYYMM'),
to_date(($1::int + 1)::text, 'YYYYMM'),
'[)');
$$;
CREATE FUNCTION

postgres=# select partial_month('201202');
partial_month
-------------------------
[2012-02-01,2012-03-01)
(1 row)

postgres=# select partial_month('201212');
partial_month
-------------------------
[2012-12-01,2013-01-01)
(1 row)

Note: it exploits to_date() parsing '200013' as '2001-01', which is
reasonable but haven't found documented and don't know how much
reliable. Writing a safer "one month later" function is left as
exercise.

-- Daniele

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:

Note: it exploits to_date() parsing '200013' as '2001-01', which is
reasonable but haven't found documented and don't know how much
reliable. Writing a safer "one month later" function is left as
exercise.

Consider adding '1 month'::interval to the month start date.

(This function relies on text-munging way too much for my taste.
There's almost always a better way to do it than that.)

regards, tom lane

On Sun, Oct 7, 2012 at 3:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:

Note: it exploits to_date() parsing '200013' as '2001-01', which is
reasonable but haven't found documented and don't know how much
reliable. Writing a safer "one month later" function is left as
exercise.

Consider adding '1 month'::interval to the month start date.

(This function relies on text-munging way too much for my taste.
There's almost always a better way to do it than that.)

Didn't realize intervals store months/days info separately: I thought
an interval was just a vector in the timestamp space. Nice surprise.

-- Daniele

I suspected that this would be the answer.

Thank you lots for your kind help, Daniele & Tom [?]

On 7 October 2012 16:46, Daniele Varrazzo <daniele.varrazzo@gmail.com>wrote: