| Bug #69287 | Subtracting "NOW() - SOME_TIME" from "NOW()" returns wrong results | ||
|---|---|---|---|
| Submitted: | 20 May 2013 13:54 | Modified: | 21 May 2013 8:05 |
| Reporter: | Timucin Gelici | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.96,5.5.x, 5.6.11 | OS: | Any (Tested on Mac OSX 10.8 and CentOS 5.x) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date, date_sub, datetime | ||
[20 May 2013 13:54]
Timucin Gelici
[20 May 2013 14:36]
Valeriy Kravchuk
I was able to repeat with 5.6.11 on Windows: C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.6.11 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:45 | 2013-05-20 17:33:00 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.19 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:47 | 2013-05-20 17:33:02 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) ... +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:53 | 2013-05-20 17:33:08 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:54 | 2013-05-20 17:33:09 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:55 | 2013-05-20 17:33:10 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:56 | 2013-05-20 17:33:11 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:57 | 2013-05-20 17:33:12 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:58 | 2013-05-20 17:33:13 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:33:59 | 2013-05-20 17:33:14 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:34:00 | 2013-05-20 17:33:15 | 85 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:34:01 | 2013-05-20 17:33:16 | 85 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_ SEC, (NOW() - DATE_SUB(NOW(), INTERVAL 45 SECOND)) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-20 17:34:08 | 2013-05-20 17:33:23 | 85 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec)
[20 May 2013 15:05]
MySQL Verification Team
Hello Timucin, Thank you for the report. Verified as described. Thanks, Umesh
[21 May 2013 8:05]
MySQL Verification Team
Hello Timucin, As per the manual and dev's this is not a bug because it seems that "-" operator is supposed to treat its arguments as numbers and not datetime values in this case. To me it seems that the code works as designed. And this fact is documented in our manual "Arithmetic operators apply to numbers. For other types of values, alternative operations may be available. For example, to add date values, use DATE_ADD()". Please reference - http://dev.mysql.com/doc/refman/5.6/en/arithmetic-functions.html So, your query should be mysql> SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 45 SECOND) AS NOW_MINUS_45_SEC, TIME_TO_SEC(TIMEDIFF(NOW(),DATE_SUB(NOW(), INTERVAL 45 SECOND))) AS FIRST_MINUS_SECOND_COLUMN; +---------------------+---------------------+---------------------------+ | NOW | NOW_MINUS_45_SEC | FIRST_MINUS_SECOND_COLUMN | +---------------------+---------------------+---------------------------+ | 2013-05-22 11:43:16 | 2013-05-22 11:42:31 | 45 | +---------------------+---------------------+---------------------------+ 1 row in set (0.00 sec)
