Thread: Partial dates

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

>
> 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)?

Well the easy solution is to just make the date a text type but that is
the wrong answer. The right answer is to fix the data set.
MySQL should never have allowed you do insert those dates in the first
place. I know that doesn't help you much but at some point
you are going to have to sanitize the data anyway.

Sincerely,

Joshua D. Drake


>
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend



--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

"Joshua D. Drake" <jd@commandprompt.com> writes:
>> Any suggestions (aside from designing a new datatype)?

> Well the easy solution is to just make the date a text type but that is
> the wrong answer. The right answer is to fix the data set.
> MySQL should never have allowed you do insert those dates in the first
> place.

I think the right answer is that a new datatype is exactly what you
need.  SQL-spec dates are absolutely *not* meant to express these
concepts (and the fact that MySQL allows you to insert "dates" with
zero fields just shows what a lack of error checking they have).

It's really not that hard to build a new datatype in PG, especially
if you only need it to store values and not do any particularly amazing
computational feats.  Consider for instance a domain over type "text"
with appropriate constraint checks.

            regards, tom lane

Joshua D. Drake wrote:
> Well the easy solution is to just make the date a text type but that is
> the wrong answer. The right answer is to fix the data set.
> MySQL should never have allowed you do insert those dates in the first
> place. I know that doesn't help you much but at some point
> you are going to have to sanitize the data anyway.

I'm not sure I agree with the need to "fix" or "sanitize" the data.  The columns
in question are used mostly for publication dates.  While you may be able to
find a full release date for recent books, they are generally listed as
published on a given year, period.  Most monthly magazines only have a
month-year of publication.  And of course, daily newspapers and Internet
articles usually have a full day-month-year.  In fact, the MySQL solution didn't
address quarterly or bi-monthly publications as that data was only captured as
one of the months in the period--as opposed to Mar/Apr 2005 or First Quarter
2005 (or worse: Winter 2004). As Tom Lane argued, it seems I'll have to bite the
bullet and create a new datatype.  The only other alternative I see would be to
split the publication date into three columns and that's rather ugly.

Thanks for the feedback.

Joe

probably you can add a new column to identify the publication type and  sanitize the wrong dates ( like 2005-00-00 to 2005-01-01). Now based on the publication type you can extract the portion of the date you want.

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

On Tue, 13 Sep 2005, Joshua D. Drake wrote:

> >
> > 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"
>
> Well the easy solution is to just make the date a text type but that is
> the wrong answer. The right answer is to fix the data set.
> MySQL should never have allowed you do insert those dates in the first
> place. I know that doesn't help you much but at some point
> you are going to have to sanitize the data anyway.
>

Hmmm... given that our real world data, (currently in a commercial RDBMS
but I have hopes :-) often has dates where we only have a month and year,
is there any way a part of a timestamp can be null? I guess this also has
indexing issues. Maybe some form of GIST would work.

Sanitizing is one thing, inventing data to fit an incomplete value into a
date datatype is not good practice.

It would need some arbitrary standard to apply date/time arithmetic &
queries. For example, if we wanted all values for 1987, a record from an
unknown day in March 1987 would be in the result set. If we wanted all
values from March 1987, similarly. All records since 13 March 1987 and the
arbitrary rule would come into play. Probably excluded because we couldn't
explicitly prove it should be included in the result set. Like other nulls
get treated.

In case anyone is interested, right now we store year, month & day and
have a timestamp field where the entire field is null if any one part is
unknown.

Are there any better ways in Postgres?



Brent Wood

On Wed, Sep 14, 2005 at 03:49:29PM +1200, Brent Wood wrote:

> In case anyone is interested, right now we store year, month & day and
> have a timestamp field where the entire field is null if any one part is
> unknown.
>
> Are there any better ways in Postgres?

You can create a new type, which is a privilege you don't have in some
other systems.  Or you can separate the column in three columns and
leave some of them NULL as appropiate.

--
Alvaro Herrera -- Valdivia, Chile         Architect, www.EnterpriseDB.com
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended."  (Gerry Pourwelle)

On Wed, 2005-09-14 at 15:49 +1200, Brent Wood wrote:
>
> Sanitizing is one thing, inventing data to fit an incomplete value into a
> date datatype is not good practice.

Choose another datatype, or make a new one, or split the date into
columns. The type of your data is not a timestamp nor a date.

In the first place, 0 is not NULL. So, even 1980-01-00 would be
different from 1980-01-NULL. For example, assuming 1980-01-00 is defined
to have some meaning, (1980-01-00 < 1980-01-02) is likely to be true,
but (1980-01-NULL < 1980-01-02) definitely is not.

You're just asking if there's a way to store a number of which the lower
bits are ignored (considered NULL). Obviously, no, you need a different
datatype or a different arrangement.

Note: the string 1980-01-00 just *looks* like a possible value, but
definitely it's not: there's simply no "space" (or time) between
1979-12-31 and 1980-01-01. It's much like trying to store sqrt(-1) into
a real. I hardly can imagine how MySQL manages to store that (the
1980-01-00, I mean).

.TM.
--
      ____/  ____/   /
     /      /       /                   Marco Colombo
    ___/  ___  /   /                  Technical Manager
   /          /   /                      ESI s.r.l.
 _____/ _____/  _/                      Colombo@ESI.it

On Wed, Sep 14, 2005 at 00:09:58 -0400,
  Joe <svn@freedomcircle.net> wrote:
>
> I'm not sure I agree with the need to "fix" or "sanitize" the data.  The
> columns in question are used mostly for publication dates.  While you may
> be able to find a full release date for recent books, they are generally
> listed as published on a given year, period.  Most monthly magazines only
> have a month-year of publication.  And of course, daily newspapers and
> Internet articles usually have a full day-month-year.  In fact, the MySQL
> solution didn't address quarterly or bi-monthly publications as that data
> was only captured as one of the months in the period--as opposed to Mar/Apr
> 2005 or First Quarter 2005 (or worse: Winter 2004). As Tom Lane argued, it
> seems I'll have to bite the bullet and create a new datatype.  The only
> other alternative I see would be to split the publication date into three
> columns and that's rather ugly.

It seems like in your case the dates are best expressed as ranges and that
you could store the information you have using an earliest possible date
and a lastest possible date. If there are extra constraints based on how
far apart the begin and end dates are you could implement them as well.