Thread: 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