MySQL Bugs: #38256: Inconsistency between unix_timestamp() differences and timestampdiff(SECOND,)

Bug #38256	Inconsistency between unix_timestamp() differences and timestampdiff(SECOND,)
Submitted:	21 Jul 2008 12:36	Modified:	23 Jul 2008 15:47
Reporter:	Adam Spragg	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.51, 5.1	OS:	Linux (Debian "Sid" package mysql-server-5.0 5.0.51a-10)
Assigned to:		CPU Architecture:	Any

Description:
When taking the difference between the unix_timestamp() of two dates, and the timestampdiff(SECOND,) between two dates, the results are different if one of the dates is in daylight savings time, and the other is not.

As far as I can tell, it is timestampdiff() that is wrong, and does not return the correct number of seconds that have elapsed between the two datetimes.

I don't think that this is a dupe of bug #15654 as that deals with the "magic hour" over which the clocks go back. The datetimes here do not fall into that magic hour.

(As a workaround, I cannot use unix_timestamp() to calculate the correct number of seconds in my application, as it uses some dates before 1970, which MySQL does not handle when converting to/from unix times)

How to repeat:
mysql> create table test (a datetime, b datetime);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test (a, b) values ('2008-03-29 12:00:00', '2008-03-30 12:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select a, b, unix_timestamp(b) - unix_timestamp(a) as diff from test;
+---------------------+---------------------+-------+
| a                   | b                   | diff  |
+---------------------+---------------------+-------+
| 2008-03-29 12:00:00 | 2008-03-30 12:00:00 | 82800 |
+---------------------+---------------------+-------+
1 row in set (0.00 sec)

mysql> select a, b, timestampdiff(SECOND, a, b) as diff from test;
+---------------------+---------------------+-------+
| a                   | b                   | diff  |
+---------------------+---------------------+-------+
| 2008-03-29 12:00:00 | 2008-03-30 12:00:00 | 86400 |
+---------------------+---------------------+-------+
1 row in set (0.00 sec)

Thank you for the report.

Please provide output of select a, b, from_unixtime(unix_timestamp(a)), from_unixtime(unix_timestamp(b)), unix_timestamp(b) - unix_timestamp(a) as diff from test; and show variables like '%time_zone';

mysql> select a, b, from_unixtime(unix_timestamp(a)) as ua, from_unixtime(unix_timestamp(b)) as ub, unix_timestamp(b) - unix_timestamp(a) as diff from test;
+---------------------+---------------------+---------------------+---------------------+-------+
| a                   | b                   | ua                  | ub                  | diff  |
+---------------------+---------------------+---------------------+---------------------+-------+
| 2008-03-29 12:00:00 | 2008-03-30 12:00:00 | 2008-03-29 12:00:00 | 2008-03-30 12:00:00 | 82800 |
+---------------------+---------------------+---------------------+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | BST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

Hope that helps.

Many thanks for reporting a bug.

That Unix timestamp difference is only 23 hours is logical, because the Unix timestamp occurs from your system time zone and that was GMT/BST. That's totally correct.

The bug is timestampdiff() or our documentation.

Our data type timestamp has no time zone. This means, that timestampdiff should not no which time zone and just would calculate: noon to noon = 24 hours.

But our data type datetime has the time zone from the variable @@time_zone and this should be respected from timestampdiff()

The question now is from where should the server know that the time_zone variable was different yesterday and today?

We have to discuss this problem internally.