MySQL Bugs: #316: Unix_Timestamp & Daylight Saving Times

Bug #316	Unix_Timestamp & Daylight Saving Times
Submitted:	23 Apr 2003 9:02	Modified:	27 May 2003 5:15
Reporter:	Johannes Püller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When using the function 'Unix_Timestamp' for an hour which occurs twice on one day because of daylight saving times the function returns different values when used in WHERE clauses or INSERT statements.

How to repeat:
Time zone on the server must be set to Europe/Vienna or a similar timezone for this example to work:

CREATE TABLE table (ts int);
INSERT INTO table (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
INSERT INTO table (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
INSERT INTO table (ts) VALUES (Unix_timestamp('2002-10-27 03:00'));
INSERT INTO table (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
SELECT * FROM table;

This returns:
1035673200
1035676800
1035684000
1035680400

Note that the second and the fourth value are different, although the function was called with the same date.

Suggested fix:
The function 'Unix_Timestamp' should always return the same value in this special case. Either for the first or the second hour.

I was able to repeat this bug with MySQL 4.0.12 so I change version in your report

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

This is NOT a bug.

Unix timestamp is not depending on daylight saving time.
(This is becasue unix timestamps is independent of timezones)

Daylight saving time comes into account when converting the timestamp to a time string.

You can verify this by doing  FROM_UNIXTIME() on the result.

Sorry, I could not find any bug report describing a similar issue, otherwise I wouldn't have reported it. Can someone tell me a bug ID please?

IMHO a function returning two different values when called the same way is indeed a bug. I am aware that Unix Timestamps are not time zone dependend, but the function should return the same value anyway, at least when called in one SQL statement twice.

We had a statement looking like this:
SELECT Unix_timestamp('2002-10-27 02:00'), * FROM table WHERE ts > Unix_timestamp('2002-10-27 02:00');

We found out, that the first call to Unix_timestamp returned another timestamp than that one in the WHERE clause.

This is of course a little more complicated to reproduce, but I would be happy to create an working example, if someone is willing to look further into it.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

I managed to come up with a fix that seams to always choose the lower timestamp when there is a conflict.

This will be in 4.0.14