List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 28 2007 1:17am
Subject:Re: BUG: DATE_ADD 99999 fails, but 9999 works.
View as plain text  
At 5:44 PM -0700 8/27/07, Chris wrote:
>I don't think this is a bug. I think what's 
>happening is that your timestamp column can't 
>hold that date, it's max value is somewhere in 
>2038.
>
>So I guess either change your timestamp column 
>to a datetime column, or prevent users from 
>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 = DATE_ADD(CURRENT_DATE(),INTERVAL
     -> 99999 DAY) WHERE CoreID = 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='traditional';
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE Users SET password_expire = 
DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY) WHERE 
CoreID = 1 LIMIT 1;
ERROR 1292 (22007): Incorrect datetime value: 
'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) 
>>unsigned NOT NULL auto_increment,
>>	`Username` varchar(155) default NULL,
>>	`Password` varchar(64) default NULL, 
>>	`password_expire` timestamp NOT NULL 
>>default '0000-00-00
>>00:00:00',
>>	PRIMARY KEY (`CoreID`)
>>) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>
>>UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9999
>>DAY) WHERE CoreID = 1 LIMIT 1;
>>
>>UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL
>>99999 DAY) WHERE CoreID = 1 LIMIT 1;
>>
>>Added as bug:
>>http://bugs.mysql.com/bug.php?id=30656
>>
>>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!
>>
>>ÐÆ5ÏÐ
>>
>>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
BUG: DATE_ADD 99999 fails, but 9999 works.Daevid Vincent28 Aug
  • Re: BUG: DATE_ADD 99999 fails, but 9999 works.Paul DuBois28 Aug
    • RE: BUG: DATE_ADD 99999 fails, but 9999 works.Daevid Vincent28 Aug
  • Re: BUG: DATE_ADD 99999 fails, but 9999 works.Chris28 Aug
    • RE: BUG: DATE_ADD 99999 fails, but 9999 works.Daevid Vincent28 Aug
      • Re: BUG: DATE_ADD 99999 fails, but 9999 works.Kirk Friggstad28 Aug
      • RE: BUG: DATE_ADD 99999 fails, but 9999 works.Daevid Vincent31 Aug
    • Re: BUG: DATE_ADD 99999 fails, but 9999 works.Paul DuBois28 Aug