Bug #36061 DATETIME math inaccuracies

• Submitted: 14 Apr 2008 14:52
• Modified: 15 Apr 2008 3:40
• Reporter: Joel Daynes
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.22
• OS: Linux (CentOS 5)
• CPU Architecture: Any

[14 Apr 2008 14:52] Joel Daynes

Description:
Performing math on a datetime column such as the following:

select sysdate()-checktime from checkin;

Occasionally yields wildly inaccurate results. I have found that functions such as TIMESTAMPDIFF are much more reliable, but it is certainly strange that the math in the query above would work much of the time, but sometimes return a value that is completely erroneous. An example is below:

mysql> desc checkin;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| hostname  | varchar(100) | YES  |     | NULL    |       | 
| checktime | datetime     | YES  |     | NULL    |       | 
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select sysdate(),checktime,sysdate()-checktime,timestampdiff(SECOND,checktime,sysdate()) from checkin;
+---------------------+---------------------+---------------------+-------------------------------------------+
| sysdate()           | checktime           | sysdate()-checktime | timestampdiff(SECOND,checktime,sysdate()) |
+---------------------+---------------------+---------------------+-------------------------------------------+
| 2008-04-11 16:55:59 | 2008-04-11 16:55:58 |            1.000000 |                                         1 | 
| 2008-04-11 16:55:59 | 2008-04-11 16:55:59 |            0.000000 |                                         0 | 
+---------------------+---------------------+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> select sysdate(),checktime,sysdate()-checktime,timestampdiff(SECOND,checktime,sysdate()) from checkin;
+---------------------+---------------------+---------------------+-------------------------------------------+
| sysdate()           | checktime           | sysdate()-checktime | timestampdiff(SECOND,checktime,sysdate()) |
+---------------------+---------------------+---------------------+-------------------------------------------+
| 2008-04-11 16:56:00 | 2008-04-11 16:55:58 |           42.000000 |                                         2 | 
| 2008-04-11 16:56:00 | 2008-04-11 16:55:59 |           41.000000 |                                         1 | 
+---------------------+---------------------+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

...

mysql> select sysdate(),checktime,sysdate()-checktime,timestampdiff(SECOND,checktime,sysdate()) from checkin;
+---------------------+---------------------+---------------------+-------------------------------------------+
| sysdate()           | checktime           | sysdate()-checktime | timestampdiff(SECOND,checktime,sysdate()) |
+---------------------+---------------------+---------------------+-------------------------------------------+
| 2008-04-11 16:56:04 | 2008-04-11 16:56:04 |            0.000000 |                                         0 | 
| 2008-04-11 16:56:04 | 2008-04-11 16:55:59 |           45.000000 |                                         5 | 
+---------------------+---------------------+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> select sysdate(),checktime,sysdate()-checktime,timestampdiff(SECOND,checktime,sysdate()) from checkin;
+---------------------+---------------------+---------------------+-------------------------------------------+
| sysdate()           | checktime           | sysdate()-checktime | timestampdiff(SECOND,checktime,sysdate()) |
+---------------------+---------------------+---------------------+-------------------------------------------+
| 2008-04-11 16:56:05 | 2008-04-11 16:56:04 |            1.000000 |                                         1 | 
| 2008-04-11 16:56:05 | 2008-04-11 16:56:05 |            0.000000 |                                         0 | 
+---------------------+---------------------+---------------------+-------------------------------------------+

How to repeat:
CREATE TABLE `checkin` (
  `hostname` varchar(100) default NULL,
  `checktime` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

My application updates the checktime column for its hostname every 5 seconds, setting it to sysdate(). If I repeatedly query using the select above (select sysdate()-checktime from checkin), I will occasionally get results that are incorrect, as shown above sample (the above test was run with 2 hosts, hence the two rows in my result). I have seen it range from 30-40s off, to one time where it was 4000s off. TIMESTAMPDIFF has worked perfectly.

[14 Apr 2008 17:44] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a, and inform about the results.

[14 Apr 2008 21:30] Joel Daynes

Same problem with 5.0.51a Community Edition:

mysql> select sysdate(),checktime,sysdate()-checktime,timestampdiff(SECOND,checktime,sysdate()) from checkin;
+---------------------+---------------------+---------------------+-------------------------------------------+
| sysdate()           | checktime           | sysdate()-checktime | timestampdiff(SECOND,checktime,sysdate()) |
+---------------------+---------------------+---------------------+-------------------------------------------+
| 2008-04-14 15:27:59 | 2008-04-14 15:27:42 |           17.000000 |                                        17 | 
| 2008-04-14 15:27:59 | 2008-04-14 15:27:42 |           17.000000 |                                        17 | 
+---------------------+---------------------+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> select sysdate(),checktime,sysdate()-checktime,timestampdiff(SECOND,checktime,sysdate()) from checkin;
+---------------------+---------------------+---------------------+-------------------------------------------+
| sysdate()           | checktime           | sysdate()-checktime | timestampdiff(SECOND,checktime,sysdate()) |
+---------------------+---------------------+---------------------+-------------------------------------------+
| 2008-04-14 15:28:00 | 2008-04-14 15:27:42 |           58.000000 |                                        18 | 
| 2008-04-14 15:28:00 | 2008-04-14 15:27:42 |           58.000000 |                                        18 | 
+---------------------+---------------------+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> select sysdate(),checktime,sysdate()-checktime,timestampdiff(SECOND,checktime,sysdate()) from checkin;
+---------------------+---------------------+---------------------+-------------------------------------------+
| sysdate()           | checktime           | sysdate()-checktime | timestampdiff(SECOND,checktime,sysdate()) |
+---------------------+---------------------+---------------------+-------------------------------------------+
| 2008-04-14 15:28:02 | 2008-04-14 15:27:42 |           60.000000 |                                        20 | 
| 2008-04-14 15:28:02 | 2008-04-14 15:27:42 |           60.000000 |                                        20 | 
+---------------------+---------------------+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

[15 Apr 2008 3:40] Valeriy Kravchuk

Please, use DATE_SUB() function to get correct results. Read http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-add for the details.

When '-' operator is used, datetimes are just converted to numbers and subtracted:

mysql> select sysdate(), sysdate()-1, sysdate()-'2008-04-15 00:00:01';
+---------------------+----------------+---------------------------------+
| sysdate()           | sysdate()-1    | sysdate()-'2008-04-15 00:00:01' |
+---------------------+----------------+---------------------------------+
| 2008-04-15 06:27:52 | 20080415062751 |                  20080415060744 |
+---------------------+----------------+---------------------------------+
1 row in set, 1 warning (0.42 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '2008-04-15 00:00:01'
1 row in set (0.00 sec)

This is not a bug, but expected and documented behaviour.