From: Paul DuBois Date: August 28 2007 1:17am Subject: Re: BUG: DATE_ADD 99999 fails, but 9999 works. List-Archive: http://lists.mysql.com/mysql/208754 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" ; format="flowed" Content-Transfer-Encoding: quoted-printable At 5:44 PM -0700 8/27/07, Chris wrote: >I don't think this is a bug. I think what's=20 >happening is that your timestamp column can't=20 >hold that date, it's max value is somewhere in=20 >2038. > >So I guess either change your timestamp column=20 >to a datetime column, or prevent users from=20 >putting invalid data in. Ahh ... yes, indeed. mysql> select DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY); +---------------------------------------------+ | DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY) | +---------------------------------------------+ | 2281-06-10 | +---------------------------------------------+ Daevid, one strategy that might work for you is to enable strict or traditional SQL mode so that you get an error if the timestamp value is out of range: mysql> UPDATE Users SET password_expire =3D DATE_ADD(CURRENT_DATE(),INTERVAL -> 99999 DAY) WHERE CoreID =3D 1 LIMIT 1; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> show warnings; +---------+------+----------------------------------------------------------= + | Level | Code | Message = | +---------+------+----------------------------------------------------------= + | Warning | 1264 | Out of range value for column 'password_expire' at row 1 = | +---------+------+----------------------------------------------------------= + 1 row in set (0.00 sec) mysql> set sql_mode=3D'traditional'; Query OK, 0 rows affected (0.01 sec) mysql> UPDATE Users SET password_expire =3D=20 DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY) WHERE=20 CoreID =3D 1 LIMIT 1; ERROR 1292 (22007): Incorrect datetime value:=20 '2281-06-10' for column 'password_expire' at row 1 > > >Daevid Vincent wrote: >>using 99999 as the DATE_ADD interval value will result in 000-00-00 but >>one less 9 will work. >> >>root# mysql --version >>mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine >>wrapper >> >>CREATE TABLE `Users` ( `CoreID` int(10)=20 >>unsigned NOT NULL auto_increment, >> `Username` varchar(155) default NULL, >> `Password` varchar(64) default NULL,=20 >> `password_expire` timestamp NOT NULL=20 >>default '0000-00-00 >>00:00:00', >> PRIMARY KEY (`CoreID`) >>) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8; >> >>UPDATE Users SET password_expire =3D DATE_ADD(CURRENT_DATE(),INTERVAL 9999 >>DAY) WHERE CoreID =3D 1 LIMIT 1; >> >>UPDATE Users SET password_expire =3D DATE_ADD(CURRENT_DATE(),INTERVAL >>99999 DAY) WHERE CoreID =3D 1 LIMIT 1; >> >>Added as bug: >>http://bugs.mysql.com/bug.php?id=3D30656 >> >>Added as a tip: >>http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html >> >>BTW, it's extremely obnoxious that when I enter in a comment on that >>page, then choose "bug" from the select box, it throws me to another >>page that says, "Sorry, but this is not the correct place to report >>bugs. You need to report bugs using our online bug reporting system. You >>can start filling out a bug report with the text you have already >>entered by clicking the following button:" ... Well why the heck did you >>let me choose that option then! UGH! >> >>=D0=C65=CF=D0 >> >> > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dpaul@stripped -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com