List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 15 1999 10:14pm
Subject:Re: unix_timestamp() and Daylight savings
View as plain text  
>>>>> "Martin" == Martin Ramsch <m.ramsch@stripped> writes:

Martin> 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.

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
Thread
unix_timestamp() and Daylight savingsSteve Ruby24 Aug
  • Re: unix_timestamp() and Daylight savingsMartin Ramsch11 Sep
    • Re: unix_timestamp() and Daylight savingsMichael Widenius16 Sep