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