MySQL Bugs: #33795: UNIX_TIMESTAMP on date field returns 23:00 previous day

Bug #33795	UNIX_TIMESTAMP on date field returns 23:00 previous day
Submitted:	10 Jan 2008 12:38	Modified:	10 Jan 2008 14:00
Reporter:	Ben Sebborn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.1.22	OS:	FreeBSD (6.2-RELEASE)
Assigned to:		CPU Architecture:	Any
Tags:	bst, DST, timestamp

Description:
Earlier today I realised that getting a timestamp of a stored date returns 23:00 of the previous day:

UNIX_TIMESTAMP('2008-07-25') returned 1216940400 which currently (as we're in GMT in the UK) equates to '2008-07-24 23:00:00' (should return 2008-07-25 00:00:00)

The problem is this:

* Date was entered into database during BST (GMT+1)
* During BST the date showed fine
* When we've moved to GMT, the timestamp now shows correct for dates in GMT, but wrong for any dates in BST 2008

The dates are stored in mysql as a date field (no times). However when I query UNIX_TIMESTAMP('2008-07-25') currently im getting a timestamp that is one hour previous to the proper date.

I've read up and now updated my system timezone files, previous to this morning tests were showing I had old timezone info on my server. I've now updated and reloaded mysql, so the following query shows the correct results:

mysql> select unix_timestamp('2008-03-30 01:00:00'), unix_timestamp('2008-03-30 02:00:00');
+---------------------------------------+---------------------------------------+
| unix_timestamp('2008-03-30 01:00:00') | unix_timestamp('2008-03-30 02:00:00') |
+---------------------------------------+---------------------------------------+
| 1206838800 | 1206838800 |
+---------------------------------------+---------------------------------------+
1 row in set

(previously the times were different)

My question is that it seems I have fixed the cause, but this hasnt changed the timestamps being returned incorrectly!

How to repeat:
UNIX_TIMESTAMP('2008-07-25')

Many thanks for writing a bug report.

This is not a bug.

UNIX_TIMESTAMP() always returns the Unix Timestamp, which is UTC. UTC is quiet similar to GMT.

Hi

We've never had this problem before?

How can we pull a timestamp from mysql that reflects the date stored? Surely 2007-06-01 should return a timestamp for this date given the timezone we're in?

Is there a way to change the timezone of the timestamp? Any other idea? Quite a show stopper this for us!

Is this related, there seems to be no workaround?::

This is because CST-related information is lost during the conversion by NOW() from the current time to a string. When presented a date string like "2004-10-31 01:52:37" which names a time that happened twice (once during daylight-saving time, and again an hour later in standard time), it doesn't know which you intend it to be interpreted as.

The docs indicate that from 4.1.3, it uses the timezone in effect at the time of the SELECT, which implies that

FROM_UNIXTIME("2004-10-31 01:52:37")

returns a different value depending on whether you are currently under daylight-saving time or not. With 4.1.2 and before, it seems to always use standard time, and hence the one-hour "error" (which is not really an error, but damn unintuitive that UNIX_TIMESTAMP(NOW()) does not return the UNIX_TIMESTAMP for now.

Note that UNIX_TIMESTAMP() without args does return the proper unix timestamp for the current time.

Look here:

select unix_timestamp('2008-07-25')\G
*************************** 1. row ***************************
unix_timestamp('2008-07-25'): 1216936800
1 row in set (0.00 sec)

$ date -j 0807250000 +%s
1216936800

my system is set to CET. Which means, 2008-07-25 CEST.
For 2008-07-25 UTC:

$ date -j -u 0807250000 +%s
1216944000

this is the same as:

$ date -j 0807250200 +%s
1216944000

in August, it's summer time, mean CEST, that's UTC+2, so this is correct.

BST is UTC+1, for 2007-08-25 BST this means:
1216940400

which is similar to to 2007-08-24 23:00 in UTC:

$ date -j -u 0807242300 +%s
1216940400

and similar 2007-08-25 1:00 CEST:
$ date -j 0807250100 +%s
1216940400

This is correct, because in the summer: UTC + 1hour = BST and BST + 1 hour = CEST

Unix timestamp is the same at the same time (moment) everywhere of the world.

Which means, your unix timestamp is the same as mine or as from somebody in New York, for example: when you have 2 o'clck, I have 3 o'clock and the New York guy has 9 o'clock.