Martin> On Tue, 1999-08-24 11:53:01 -0600, Steve Ruby wrote:

mysql> select unix_timestamp('1999/04/04 02:00:00');

mysql> select unix_timestamp('1999-04-04 02:00:00');

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

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

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

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

<cut>

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

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

Martin> Regards, Martin> Martin

Hi!

The main problem here is that I haven't found a single 'reliable' function that can convert a localtime back to a timestamp.

MySQL does the conversion from date format -> timestamp by first trying to calculate the exact time and then in a loop call 'localtime()' with better and better approximations until it finds a time that returns the original time or the loop aborts because it detected a possible endless loop.

(This usually fixes most daylight saving time and timezone problems)

Here is the code (from sql/time.cc):

long my_gmt_sec(TIME *t) { time_t tmp; struct tm *l_time,tm_tmp; long diff;

if (t->hour >= 24) { /* Fix for time-loop */ t->day+=t->hour/24; t->hour%=24; } tmp=(time_t) ((calc_daynr((uint) t->year,(uint) t->month,(uint) t->day) - (long) days_at_timestart)*86400L + (long) t->hour*3600L + (long) (t->minute*60 + t->second)) + (time_t) my_time_zone; localtime_r(&tmp,&tm_tmp); l_time=&tm_tmp; for (uint loop=0; loop < 3 && t->hour != (uint) l_time->tm_hour ; loop++) { /* One check should be enough ? */ diff=3600L*(long) ((((int) (t->hour - l_time->tm_hour)+36) % 24)-12); my_time_zone+=diff; tmp+=(time_t) diff; localtime_r(&tmp,&tm_tmp); l_time=&tm_tmp; } if ((my_time_zone >=0 ? my_time_zone: -my_time_zone) > 3600L*12) my_time_zone=0; /* Wrong date */ return tmp; } /* my_gmt_sec */

Any suggestions how to do this better ?

Regards, Monty