Re: HOW-TO do incomplete dates: year, optional month, optional day?

On Wednesday 22 Jan 2003 10:11 pm, will trillich wrote:
> is there a way, save lots of manual manipulation and
> hand-waving, to implement PARTIAL DATES? (this may be exactly
> the job for creating a new data type, but hopefully someone's
> already invented this wheel...?)
>
>     "doug has worked at pinnacle since 1991".
>
> not 1-jan-1991, not 31-dec-1991, but just plain-ol' 1991.
>
>     "beulah started here back in november of 1998."
>
> not 1-november, not 30-november. just november, of 1999.
>
>     "my first day at acme corp was the 17th of may, 2001."
>
> here, in that same field, we need year, month AND day.
>
> at the moment i'm considering views and rules (with plpgsql
> functions to do the re-assembly). here's a seat-of-the-pants
> recreation (no syntax checking) to show my gyrations:
>
> CREATE TABLE partial_dates(
>     -- yada yada
>     partial_year  INTEGER, -- zero/null or 1492, 2001...
>     partial_month SMALLINT,-- zero/null, or 1-12
>     partial_day   SMALLINT -- zero/null, or 1-31
>     -- yada yada
> );

The only other thing I can think of would be to store it all as an INT4, so
for the examples above you'd store

19910000
19981100
20010517

So you're using the zeroes as n/a but still keeping the value as one column.
Add a check function valid_partial_date(..) and a display fn
show_partial_date(...)

The other alternative would be to store a text representation of the date, so
you don't need to translate when viewing, but that would mean more parsing
when checking new values.

PS - I'd use the new CREATE DOMAIN feature of 7.3 to name the type ready for
checks on domains in a later release - when that's done it'll be much
cleaner.

--
  Richard Huxton