MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Martijn Engler Date:April 21 2009 9:09am
Subject:Re: UNIX_TIMESTAMP - Can anyone explain this behavior?
View as plain text  
Hi Keith,

I'm not sure, but this might be DST that's in your way. Have you
looked into that?

Have a nice day,

- Martijn

On Mon, Apr 20, 2009 at 18:34, Keith Hughitt <keith.hughitt@stripped> wrote:
> 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