On Fri, Aug 22, 2003 at 01:16:48AM -0500, Hans van Harten wrote:
> Rajesh Kumar wrote:
> > Peter Brawley unknowingly asked us:
> >>> Interestingly, use of date_add() and date_sub() on 'odd' dates such
> >>> as "Feb 31" does produce sane results.
> >>> Subtract one from "2000 Feb 31", and you'll get 2000-03-01.
> >> This is sane!!??
> > This is where Unix Timestamps come into action (and perhaps rescue)!
> > To be sure that we're storing a sane value in our DB, we could use:
> > SELECT unix_timestamp('2002-02-31');
> > which provides a correct result.
> > Some interesting results:
> > We could then probably eliminate all timestamps stored as 1969-12-31
> > as we know that they're illegal.
> Uh, isn't it an signed integer, thus allowing date before 1970-01-01 too??
>
> Anyway, all this led me towards is:
> create database data_test ;
> use data_test;
> create table test3 (a date);
> insert into test3 values (
> from_unixtime(unix_timestamp('2002-1111102-31'),'%Y-%m-%d' ));
> insert into test3 values (
> from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
> insert into test3 values (
> from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
> insert into test3 values ( from_unixtime(-2147483646));
> select * from test3;
> still resulting in a mash:
> 1970-01-01
> 1996-03-01
> 1970-01-01
> 1900-01-00
> that comes with neither warnings nor errors reported ...
>
Error reports would have been nice, but why does your application supply
these incorrect dates anyway? Probably because some user typed them. And
what are you going to tell the user when you get a generic MySQL error
for an INSERT with many values? 'Oops, something went wrong'?
Just check the values in your application, like you should do for any
value coming from an external source. Then you can tell the user exactly
what went wrong for each field. It also prevents garbage or badly
formatted data from entering your database.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
