MySQL Bugs: #15354: Adding seconds to new-style TIMESTAMP field

Bug #15354	Adding seconds to new-style TIMESTAMP field
Submitted:	30 Nov 2005 16:16	Modified:	1 Dec 2005 19:28
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.6	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
n MySQL 3.23 you could set a timestamp field like so:

UPDATE user SET expiry=NOW()+900

where 900 is in seconds. It also worked for much higher values like NOW()+259200 (ie, 72 hours).
In MySQL 4.1.5 it still works for small values (such as 900), but the update is silently ignored for larger values (such as 259200). I don't know if this is deliberate or a bug!

A work around is to use the new ADDDATE(NOW(),3) function to add time in days.

How to repeat:
UPDATE user SET expiry=NOW()+900 works
UPDATE user SET expiry=NOW()+259200 doesn't work

Thank you for a problem report. Please, describe what do you mean by:

UPDATE user SET expiry=NOW()+259200 doesn't work?

You may get a valid timestamp value, you may also get invalid one. Please, note, that the result of NOW() + something is simply a number. And, when you put it back to timestamp column, you may got a problem.

Please, read the manual on new behaviour of timestamps in 4.1: http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html

UPDATE user SET expiry=NOW()+259200 doesn't work - as I said above, it is silently ignored. No error is given.

I don't see how NOW()+259200 could be an invalid timestamp value - 259200 seconds is just 72 hours in the future. It worked fine with MySQL v3.23.
As I said, smaller numbers of seconds, like NOW()+900 work fine with v4.1.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Sorry to say that, but you are wrong.

NOW() + NNN was never correct way to increase timestamp in *any* MySQL release.

MySQL treats temporal data as strings and in case when they are used in numeric context temporal data are converted to Integers with format as follows:

YYYYMMDDhhmmss
  for DATETIME and TIMESTAMP
YYYYMMDD 
  for DATE
hhmmss
  for TIME

You can easily see that with:

mysql> SELECT NOW(), NOW()+0;
+---------------------+----------------+
| NOW()               | NOW()+0        |
+---------------------+----------------+
| 2005-12-01 21:25:18 | 20051201212518 |
+---------------------+----------------+

Adding *any* number to the above may produce valid resultby pure chance only.

The only valid way to achieve correct result is to use DATE_ADD() function (as you noted already) 

DATE_ADD(NOW(), INTERVAL 900 SECOND)
or
NOW()+ INTERVAL 900 SECOND;