Thread: this date format thing.
OK, this has been bugging me for days. One of the things I like about Postgresql is that if you give it a data constraint it follows it. Sometimes it even seems a bit overzealous, but I'd rather fix my code than the user's data. So, this thing with the date fields not being able to be forced into a certain format is kinda bothersome to me. No matter how much people argue for the application code being the gate master, I'm much happier when I know my database won't swallow things it shouldn't. While I don't mind writing my own trigger to handle this, it seems like for joe average user there should be some strict setting that can be applied to force the dates to be in the right order format or to be assumed to be bad and tossed out. While situations where 04-02 would get put in when the user meant 02-04, at least enough of the 02-13 cases would throw an error that the user would know they had been doing something wrong along the way and would hopefully go back and look at their data. The way Postgresql deals with this kinda reminds me of the way MySQL deals with data that's out of bounds.
I feel the same way. What does the SQL standards say, gurus? scott.marlowe wrote: > OK, this has been bugging me for days. One of the things I like about > Postgresql is that if you give it a data constraint it follows it. > Sometimes it even seems a bit overzealous, but I'd rather fix my code than > the user's data. > > So, this thing with the date fields not being able to be forced into a > certain format is kinda bothersome to me. No matter how much people argue > for the application code being the gate master, I'm much happier when I > know my database won't swallow things it shouldn't. > > While I don't mind writing my own trigger to handle this, it seems like > for joe average user there should be some strict setting that can be > applied to force the dates to be in the right order format or to be > assumed to be bad and tossed out. > > While situations where 04-02 would get put in when the user meant 02-04, > at least enough of the 02-13 cases would throw an error that the user > would know they had been doing something wrong along the way and would > hopefully go back and look at their data. > > The way Postgresql deals with this kinda reminds me of the way MySQL deals > with data that's out of bounds. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Wed, 2 Apr 2003 13:23:45 -0700 (MST), scott.marlowe wrote:
> While situations where 04-02 would get put in when the user meant 02-04,
> at least enough of the 02-13 cases would throw an error that the user
> would know they had been doing something wrong along the way and would
> hopefully go back and look at their data.
You wish.
jnlstats=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
jnlstats=> set datestyle='ISO';
SET
jnlstats=> select '2001-12-31'::date;
date
------------
2001-12-31
(1 row)
jnlstats=> select '2001-31-12'::date;
date
------------
2001-12-31
(1 row)
jnlstats=> select to_date('2001-31-12','yyyy-mm-dd');
to_date
------------
2003-07-14
(1 row)
--
Peter Haworth pmh@edison.ioppublishing.com
"True, in chess you never get to grab your opponent, roll him around
your hip and slam him (or her) to the floor but you get the idea"
-- Simon Wistow
On Thu, Apr 03, 2003 at 12:36:07PM +0100, Peter Haworth wrote:
> On Wed, 2 Apr 2003 13:23:45 -0700 (MST), scott.marlowe wrote:
> > While situations where 04-02 would get put in when the user meant 02-04,
> > at least enough of the 02-13 cases would throw an error that the user
> > would know they had been doing something wrong along the way and would
> > hopefully go back and look at their data.
>
> You wish.
>
> jnlstats=> select '2001-31-12'::date;
> date
> ------------
> 2001-12-31
> (1 row)
>
> jnlstats=> select to_date('2001-31-12','yyyy-mm-dd');
> to_date
> ------------
> 2003-07-14
> (1 row)
Are you sure is there 31 months ('mm')? I think better is:
test=# select to_date('2001-31-12','yyyy-dd-mm');
to_date
------------
2001-12-31
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
On Thu, 3 Apr 2003 16:17:05 +0200, Karel Zak wrote:
> On Thu, Apr 03, 2003 at 12:36:07PM +0100, Peter Haworth wrote:
> >
> > jnlstats=> set datestyle='ISO';
> > SET
> >
> > jnlstats=> select '2001-31-12'::date;
> > date
> > ------------
> > 2001-12-31
> > (1 row)
> >
> > jnlstats=> select to_date('2001-31-12','yyyy-mm-dd');
> > to_date
> > ------------
> > 2003-07-14
> > (1 row)
>
> Are you sure is there 31 months ('mm')?
No. That's the point. Those examples are of dates being accepted which
shouldn't be. If someone enters a date with the month and day swapped,
postgres doesn't necessarily reject it.
When casting text to dates, if it can be made to validate by swapping
day and month, that's what postgres does. This makes both yyyy-mm-dd
and yyyy-dd-mm formats valid for input, which means that the application
can't trust that the user is entering the date they think they are.
When using to_date(), there seems to be no range checking at all. This
is even worse than the above, which will at least reject strings if they
don't fit into any date format. to_date() seems to treat the month as
"number of months since the beginning of the specified year" rather than
"calendar month within the specified year".
--
Peter Haworth pmh@edison.ioppublishing.com
End users will report anything as a bug:
"Your web site sucks because it didn't work after I put jelly in my keyboard."
-- Mark Jason Dominus
On Thu, Apr 03, 2003 at 03:40:59PM +0100, Peter Haworth wrote:
> On Thu, 3 Apr 2003 16:17:05 +0200, Karel Zak wrote:
> > On Thu, Apr 03, 2003 at 12:36:07PM +0100, Peter Haworth wrote:
> > >
> > > jnlstats=> set datestyle='ISO';
> > > SET
> > >
> > > jnlstats=> select '2001-31-12'::date;
> > > date
> > > ------------
> > > 2001-12-31
> > > (1 row)
> > >
> > > jnlstats=> select to_date('2001-31-12','yyyy-mm-dd');
> > > to_date
> > > ------------
> > > 2003-07-14
> > > (1 row)
> >
> > Are you sure is there 31 months ('mm')?
>
> No. That's the point. Those examples are of dates being accepted which
> shouldn't be. If someone enters a date with the month and day swapped,
> postgres doesn't necessarily reject it.
>
> When using to_date(), there seems to be no range checking at all. This
> is even worse than the above, which will at least reject strings if they
> don't fit into any date format. to_date() seems to treat the month as
> "number of months since the beginning of the specified year" rather than
> "calendar month within the specified year".
Hmm, You're probably right that to_date/timestamp is too optimistic
and liberal if you want to use it for data checking. Oracle:
SVRMGR> select to_date('2001-31-12','yyyy-mm-dd') from dual;
TO_DATE('
---------
ORA-01843: not a valid month
I will fix it in to_date/timestamp in 7.4. Thanks.
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
YEAH! Also, the casting, can it be fixed, to be less forgiving and not making
assumptions?
Karel Zak wrote:
> On Thu, Apr 03, 2003 at 03:40:59PM +0100, Peter Haworth wrote:
>
>>On Thu, 3 Apr 2003 16:17:05 +0200, Karel Zak wrote:
>>
>>>On Thu, Apr 03, 2003 at 12:36:07PM +0100, Peter Haworth wrote:
>>>
>>>>jnlstats=> set datestyle='ISO';
>>>>SET
>>>>
>>>>jnlstats=> select '2001-31-12'::date;
>>>> date
>>>>------------
>>>> 2001-12-31
>>>>(1 row)
>>>>
>>>>jnlstats=> select to_date('2001-31-12','yyyy-mm-dd');
>>>> to_date
>>>>------------
>>>> 2003-07-14
>>>>(1 row)
>>>
>>> Are you sure is there 31 months ('mm')?
>>
>>No. That's the point. Those examples are of dates being accepted which
>>shouldn't be. If someone enters a date with the month and day swapped,
>>postgres doesn't necessarily reject it.
>>
>>When using to_date(), there seems to be no range checking at all. This
>>is even worse than the above, which will at least reject strings if they
>>don't fit into any date format. to_date() seems to treat the month as
>>"number of months since the beginning of the specified year" rather than
>>"calendar month within the specified year".
>
>
> Hmm, You're probably right that to_date/timestamp is too optimistic
> and liberal if you want to use it for data checking. Oracle:
>
> SVRMGR> select to_date('2001-31-12','yyyy-mm-dd') from dual;
> TO_DATE('
> ---------
> ORA-01843: not a valid month
>
>
> I will fix it in to_date/timestamp in 7.4. Thanks.
>
> Karel
>
>
On Thu, 3 Apr 2003, Peter Haworth wrote:
> On Wed, 2 Apr 2003 13:23:45 -0700 (MST), scott.marlowe wrote:
> > While situations where 04-02 would get put in when the user meant 02-04,
> > at least enough of the 02-13 cases would throw an error that the user
> > would know they had been doing something wrong along the way and would
> > hopefully go back and look at their data.
>
> You wish.
>
> jnlstats=> select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
>
> jnlstats=> set datestyle='ISO';
> SET
> jnlstats=> select '2001-12-31'::date;
> date
> ------------
> 2001-12-31
> (1 row)
>
> jnlstats=> select '2001-31-12'::date;
> date
> ------------
> 2001-12-31
> (1 row)
>
> jnlstats=> select to_date('2001-31-12','yyyy-mm-dd');
> to_date
> ------------
> 2003-07-14
> (1 row)
No, I was saying that if we fixed the parser that would happen. I know
it's horribly broken right now. Who hired Monty to work on our date
parsing routine anyway? :-)