>>>>> "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
