Partial dates

From: Joe <svn(at)freedomcircle(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Partial dates
Date: 2005-09-14 01:44:42
Message-ID: 4327808A.3090801@freedomcircle.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm converting a MySQL database to PostgreSQL. Two of the tables have DATE
columns which have many "partial" dates. For example, a partial date may be for
the publication date of a book, where the date is specified as only the year,
e.g., 1957-00-00, and another partial date may be the publication date of a
periodical specified as a month and year, e.g., 2005-03-00. MySQL accepts these
apparently invalid or incomplete dates, but when I try to copy them into
Postgres, I get errors like

ERROR: date/time field value out of range: "1997-10-00"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY Entry, line 1, column EntryDate: "1997-10-00"

I read Appendix B and section 8.5, but I didn't find any way around this, i.e.,
it seems Postgres insists on complete dates with no zero day of month or month.
Changing the zeros to ones would be major editing task and the application
code would still have trouble distinguishing whether 2005-03-01 meant March 2005
(a monthly publication date) or 1st March 2005 (a date of an article published
on that date or of a weekly periodical) (because right now it interprets the
zero day of month as the former). Any suggestions (aside from designing a new
datatype)?

Joe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Schuerig 2005-09-14 01:46:21 Re: Setting date format for a single output column?
Previous Message Irfan Syukur 2005-09-14 01:31:02 MS SQL - PostgreSQL