List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 11 1999 4:33pm
Subject:Re: unix_timestamp() and Daylight savings
View as plain text  
On Tue, 1999-08-24 11:53:01 -0600, Steve Ruby wrote:
> Using 3.22.24 on NT
> 
> I get
> 
> mysql> select unix_timestamp('1999/04/04 02:00:00');
> +---------------------------------------+
> | unix_timestamp('1999/04/04 02:00:00') |
> +---------------------------------------+
> |                             923216400 |
> +---------------------------------------+
> 1 row in set (0.00 sec)
> 
> mysql> select unix_timestamp('1999-04-04 02:00:00');
> +---------------------------------------+
> | unix_timestamp('1999-04-04 02:00:00') |
> +---------------------------------------+
> |                             923212800 |
> +---------------------------------------+
> 1 row in set (0.00 sec)
> 
> 
> normaly unix_timestamp('yyyy-mm-dd HH:mm:ss')
> is equal to the same with "/" instead of "-" with
> the exception of hours that were within my Daylight
> savings time changeover. Hours before and after
> are fine.

Using MySQL 3.22.19b on Solaris 2.6, I also do get strange results
for these points in time, that are within the Daylight savings time
changeover.

For my timezone +0100 (= CET = MET = MEZ), after 1999-03-28 01:59:59
the next second was 1999-03-28 03:00:00, so the hour from 2am to 3am
is non-existant in local time.

    mysql> select unix_timestamp('1999-03-28 02:00:00');
    +---------------------------------------+
    | unix_timestamp('1999-03-28 02:00:00') |
    +---------------------------------------+
    |                             922582800 |
    +---------------------------------------+
    
    mysql> select unix_timestamp('1999-03-28 02:00:00');
    +---------------------------------------+
    | unix_timestamp('1999-03-28 02:00:00') |
    +---------------------------------------+
    |                             922579200 |
    +---------------------------------------+
    
    mysql> select unix_timestamp('1999-03-28 02:00:00');
    +---------------------------------------+
    | unix_timestamp('1999-03-28 02:00:00') |
    +---------------------------------------+
    |                             922582800 |
    +---------------------------------------+
    
    mysql> select unix_timestamp('1999-03-28 02:00:00');
    +---------------------------------------+
    | unix_timestamp('1999-03-28 02:00:00') |
    +---------------------------------------+
    |                             922579200 |
    +---------------------------------------+

That means, the result toggles between these two values with
difference 3600 sec = 1 hour.


For the next changeover back to normal time, that is when on
1999-10-31 03:00:00 local time clocks are set back to 1999-10-31
02:00:00 local time, effectively meaning that the hour from 2pm to 3pm
does exist twice in local time, MySQL's unix_timestamp always gives
the values of the later hour.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
unix_timestamp() and Daylight savingsSteve Ruby24 Aug
  • Re: unix_timestamp() and Daylight savingsMartin Ramsch11 Sep
    • Re: unix_timestamp() and Daylight savingsMichael Widenius16 Sep