Bug #3582 unix_timestamp(date) miscalculation

• Submitted: 28 Apr 2004 9:39
• Modified: 28 Apr 2004 11:25
• Reporter: Lars Rönnbäck
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.17
• OS: Linux (Linux)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[28 Apr 2004 9:39] Lars Rönnbäck

Description:
unix_timestamp seems to lose an hour every now and then:

mysql> select (unix_timestamp('2003-03-31 00:00:00') - unix_timestamp('2003-03-30 04:00:00'))/60/60 as hours;
+---------+
| hours   |
+---------+
| 20.0000 |
+---------+
1 row in set (0.01 sec)

mysql> select (unix_timestamp('2003-03-31 00:00:00') - unix_timestamp('2003-03-30 03:00:00'))/60/60 as hours;
+---------+
| hours   |
+---------+
| 21.0000 |
+---------+
1 row in set (0.00 sec)

mysql> select (unix_timestamp('2003-03-31 00:00:00') - unix_timestamp('2003-03-30 02:00:00'))/60/60 as hours;
+---------+
| hours   |
+---------+
| 21.0000 |
+---------+
1 row in set (0.00 sec)

mysql> select (unix_timestamp('2003-03-31 00:00:00') - unix_timestamp('2003-03-30 01:00:00'))/60/60 as hours;
+---------+
| hours   |
+---------+
| 22.0000 |
+---------+
1 row in set (0.00 sec)

How to repeat:
See example in description

[28 Apr 2004 11:25] Sergei Golubchik

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:

No bug here.
2003-03-30 02:00:00 - 2003-03-30 03:00:00 is where daylight saving occurs (in your timezone). 3:00 follows immediately after 2:00.
E.g. 2003-03-30 02:30:00 is an illegal timestamp - there was no 2:30 at 30th of March, 2003.