Thread: Partial or incomplete dates

Hello all,
this is my first posting to this list. I have been using MySQL for a few
years with a web application for displaying my genealogy database, a
FoxPro database from The Master Genealogist from which I'm dumping a
subset with a Perl script and the DBD::XBase module. I'm now in the
process of developing my own genealogy program, and I have been
absolutely thrilled by the prospect of using PostgreSQL for this
venture.

My first stumbling block is this: How do I store incomplete or partial
dates in PostgreSQL? I ran a Google search on 'postgresql "partial
dates"' and came up with this thread as the only significant match:
<url:http://archives.postgresql.org/pgsql-general/2003-01/msg00888.php>

In MySQL, the checking on dates is very relaxed, so it's totally legal
to enter a date as '1731-00-00', and let your own program logic decide
that this means just the year 1731. Do I have to make my own data type
to store and manipulate partial dates, or has somebody already done it?
Or is there a way to use relaxed date checking in PostgreSQL?
--
Leif Biberg Kristensen
http://solumslekt.org/

On Sat, Mar 12, 2005 at 11:30:03 +0100,
  "Leif B. Kristensen" <leif@solumslekt.org> wrote:
>
> In MySQL, the checking on dates is very relaxed, so it's totally legal
> to enter a date as '1731-00-00', and let your own program logic decide
> that this means just the year 1731. Do I have to make my own data type
> to store and manipulate partial dates, or has somebody already done it?
> Or is there a way to use relaxed date checking in PostgreSQL?

There is no existing datatype like that. Probably the best thing to do
is to store the year, month and day as separate integers and allow for
nulls or special codes to indicate missing data.