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
