Bug #2232 now()-XXX give incorrect date time

• Submitted: 29 Dec 2003 1:37
• Modified: 29 Dec 2003 2:11
• Reporter: Chow Ka Hong
• Status: Not a Bug
• Category: MySQL Server: Command-line Clients
• Severity: S2 (Serious)
• Version: 3.23.47-nt
• OS: Windows (Win 2000)
• CPU Architecture: Any

[29 Dec 2003 1:37] Chow Ka Hong

Description:
I would like to use now()-XXX to calculate the time before now, but some error occur as follow (three sucess case and three error case):

Logging to file 'c:\a.txt'
mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2003-12-29 17:22:24 |
+---------------------+
1 row in set (0.00 sec)

mysql> select NOW(),now()-30;
+---------------------+----------------+
| NOW()               | now()-30       |
+---------------------+----------------+
| 2003-12-29 17:22:38 | 20031229172208 |   <---OK
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select NOW(),now()-30;
+---------------------+----------------+
| NOW()               | now()-30       |
+---------------------+----------------+
| 2003-12-29 17:22:49 | 20031229172219 |   <----OK
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select NOW(),now()-30;
+---------------------+----------------+
| NOW()               | now()-30       |
+---------------------+----------------+
| 2003-12-29 17:22:56 | 20031229172226 |  <----OK
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select NOW(),now()-30;
+---------------------+----------------+
| NOW()               | now()-30       |
+---------------------+----------------+
| 2003-12-29 17:23:02 | 20031229172272 |  <---Error case, the second become 72!!
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select NOW(),now()-30;
+---------------------+----------------+
| NOW()               | now()-30       |
+---------------------+----------------+
| 2003-12-29 17:23:06 | 20031229172276 |  <---Error case, the second become 76!!
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select NOW(),now()-30;
+---------------------+----------------+
| NOW()               | now()-30       |
+---------------------+----------------+
| 2003-12-29 17:23:09 | 20031229172279 |  <---Error case, the second become 79!!
+---------------------+----------------+
1 row in set (0.01 sec)

mysql> \t

How to repeat:
If substract the time to one mintue before, the second of time has error

[29 Dec 2003 2:11] Alexander Keremidarski

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:

In a way you are using it DATETIME values are converted to integer before subtraction 

This is well explained in the manual.

What you need is to read about DATE_ADD/DATE_SUB functions:

mysql> SELECT NOW(), NOW() - INTERVAL 30 SECOND;
+---------------------+----------------------------+
| NOW()               | NOW() - INTERVAL 30 SECOND |
+---------------------+----------------------------+
| 2003-12-29 12:11:04 | 2003-12-29 12:10:34        |
+---------------------+----------------------------+