Re: Question about PARTIAL DATE type/s

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.

Thanks,

RD

On Sun, Oct 7, 2012 at 12:28 PM, r d <rd0002(at)gmail(dot)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(dot)varrazzo(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)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(dot)varrazzo(at)gmail(dot)com>wrote:

> On Sun, Oct 7, 2012 at 3:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)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
>