Re: A creepy story about dates. How to prevent it?

(Comments from the peanut gallery here)

> > > IMHO it is a bug. We don't let postgresql "guess" about a lot of more
> > > obvious things (i.e. int4 to int8 casting, etc...) and letting it guess
> > > about dates makes it non-ACID compliant.
> >
> > How do you arrive at that conclusion?
>
> The same way I come to all my conclusions, logic. :-) but seriously...
>
> Why not accept a date of 04/44/2003 and just wrap it into May? It's
> the same kind of thing.

Is it? Similar, perhaps, but at least you can be pretty sure that 44 and
2003 are not valid months. (Not that I want the database fixing that for
me, either.)

> I told my database where I live, and expect it to
> only accept dates that are valid in my locale.

I wouldn't suggest that. Locale is not dependable because there is
simply no dependable way of mapping, for example, IP address to a
physical location, much less to a cultural location.

Also, even if you had a truly accurate way to determine that your user
was Japanese, you wouldn't know whether the user intended western era or
Japanese era, whether the user was doing year-month-day according
tradition here, or whether the user was trying to anticipate a western
order because the web site was in English.

(I'd give about a 90% odds that the date 01.02.03 entered into a web
site by someone Japanese is going to mean 3 Feb 2001, 8% that it's going
to mean 3rd of February of the year Heisei 1, and 2% that the user is
going to try to second-guess and enter it in one of the western orders.)

In the US, you will also have users who may be accustomed to using
military or genealogy order, as well.

> If a user feeds it a date
> that isn't right, I expect the database to error out.

I personally wouldn't want the order checked in the database. I think
I'd prefer that the application set the order and that the database
limit itself to range-checking the elements.

> > > If it isn't a bug, how do I implement a check constraint to stop it from
> > > happening?

Good question. Another good question is how the database would implement
the check.

> > > I'd like to know my database accepts properly formatted input
> > > and rejects the rest. That's what the C in ACID means, right?
> >
> > Do the checking in your application.
>
> I do. I make sure it's ##/##/#### (i.e. a simple regex works)

That's not much of a check, of course.

> The database already does the rest of the checking for me, it just happens
> to think it might be helpful to coerce some bad dates for me, but others
> that are obviously wrong are tossed out.

If this means what it appears to mean, that, when the database is
expecting day-month-year, it "fixes" 02/22/2003 to 2003.FEB.22, that
would worry me. I'd prefer that sort of behavior to be off by default.
I'd much rather have it just cough on a month 22. If attempted, I'd want
it in the application layer.

> Here's a scenario for how we can wind up teaching a user to enter dates
> the wrong way. The day is 22 feb. They enter this date, in the US, where
> mm/dd/yyyy is standard:
>
> 22/02/2003
>
> The database converts it to
>
> 02/22/2003 silently.

The application layer should report, explicitly, how it interpreted what
was entered. Explicity feedback is the only way to make reasonably sure
the user and the database are on the same wavelength.

> ...
> We continue the rest of the year this way. Somewhere along the line, the
> user notices all their reports have the wrong date. Which ones were for
> feb 03 and which ones were for march 02? We don't know.
>
> Why bother checking for wrong dates sometimes (i.e. 03/33/2003) but not
> all the time (i.e. 13/03/2003)?
>
> > Something you think is improperly
> > formatted probably shouldn't get to the database in the first place.
>
> Agreed. But that's not the point. It is properly formatted, i.e.
> mm/dd/yyyy, it's just out of range. That's not the same at all.

I think order would still be format, which is why I wouldn't want the
database checking it, and especially not trying to fix it.

>...
> > I do now seem to recall an agreement that a GUC switch to disable
> > date-interpretation guessing would be okay, though.
>
> I'm pretty sure it was the other way around, make strict locale / date
> checking the standard and a GUC to turn it off for folks who really want
> to use a broken database. :-)

I would not want the database guessing the order from the locale, either.

My JPY 2.

--
Joel Rees, programmer, Kansai Systems Group
Altech Corporation (Alpsgiken), Osaka, Japan
http://www.alpsgiken.co.jp