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
Triage: Triaged: D2 (Serious)

[21 Jul 2008 12:36] Adam Spragg
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)
[21 Jul 2008 18:45] Sveta Smirnova
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';
[23 Jul 2008 12:09] Adam Spragg
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.
[23 Jul 2008 15:47] Susanne Ebrecht
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.