List:General Discussion« Previous MessageNext Message »
From:Keith Hughitt Date:April 20 2009 4:34pm
Subject:UNIX_TIMESTAMP - Can anyone explain this behavior?
View as plain text  
Hi all,

Does anyone know what is going on here:

//Query:

select UNIX_TIMESTAMP(TIMESTAMP('2003-01-01 00:00:00')) as first,
UNIX_TIMESTAMP(TIMESTAMP('2003-10-05 00:00:00')) as second,
UNIX_TIMESTAMP(TIMESTAMP('2004-01-01 00:00:00')) as third;

+------------+------------+------------+
| first      | second     | third      |
+------------+------------+------------+
| 1041400800 | 1065330000 | 1072936800 |
+------------+------------+------------+

// Converting timestamps to UTC using linux date command (could also use
http://www.4webhelp.net/us/timestamp.php)

$ date -u -d @1072936800
Thu Jan  1 06:00:00 UTC 2004

$ date -u -d @1041400800
Wed Jan  1 06:00:00 UTC 2003

$ date -u -d @1064984400
Wed Oct  1 05:00:00 UTC 2003

MySQL seems to treat the local time as being UTC -6 hours in the first two
cases but as UTC -5 in other cases. The system local time appears to be
UTC-5 (EST):

// Attempting to determine MySQL's timezone offset:

select UNIX_TIMESTAMP(UTC_TIMESTAMP()) - UNIX_TIMESTAMP(now()) as offset:

+--------+
| offset |
+--------+
|  18000 |
+--------+

which is consistent with the last result, but not the first two.

I have not yet tested more dates throughout the year to see when the change
occurs, and if there is a pattern, but I though I'd ask first to see if
anyone else has either
encountered this before, or knows what is going on?

I would like to be able to store some UTC datetimes in a system that uses
localtime, and then extract them as UTC timestamps again, which is why I'm
trying to figure
out the proper offset. On this particular system I also do not have the
ability to change the default timezone (e.g. to UTC/GMT), so I'm stuck with
using local dates.

Any suggestions? Any help would be greatly appreciated :)

Thanks!
-Keith

Thread
UNIX_TIMESTAMP - Can anyone explain this behavior?Keith Hughitt20 Apr
  • Re: UNIX_TIMESTAMP - Can anyone explain this behavior?Martijn Engler21 Apr