List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 13 2000 3:06am
Subject:unix_timestamp() incorrect in some timezones.
View as plain text  
Hi!

>>>>> "jon" == jon  <jon@stripped> writes:

>> Description:
jon> 	unix_timestamp() function fails in timezones with half hour offsets.
>> How-To-Repeat:

<cut>
jon> Type 'help' for help.

mysql> select unix_timestamp('2000-02-03 01:05:00');
jon> +---------------------------------------+
jon> | unix_timestamp('2000-02-03 01:05:00') |
jon> +---------------------------------------+
jon> |                             949503900 |
jon> +---------------------------------------+
jon> 1 row in set (0.00 sec)

mysql> select unix_timestamp('2000-02-03 01:55:00');
jon> +---------------------------------------+
jon> | unix_timestamp('2000-02-03 01:55:00') |
jon> +---------------------------------------+
jon> |                             949503300 |
jon> +---------------------------------------+
jon> 1 row in set (0.00 sec)

mysql> Bye

jon> Note that 1:55 is later than 1:05, but the unix_timestamp() is 600 seconds less!
jon> Compare these values with timestamps read at the unix shell and note that both 
jon> have different values:

I have now fixed this for MySQL 3.23.11:

Here is an example of this:

mysql> select unix_timestamp('2000-02-03 01:05:00');
+---------------------------------------+
| unix_timestamp('2000-02-03 01:05:00') |
+---------------------------------------+
|                             949502100 |
+---------------------------------------+
1 row in set (0.01 sec)

mysql> select unix_timestamp('2000-02-03 01:55:00');
+---------------------------------------+
| unix_timestamp('2000-02-03 01:55:00') |
+---------------------------------------+
|                             949505100 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(unix_timestamp('2000-02-03 01:55:00'));
+------------------------------------------------------+
| from_unixtime(unix_timestamp('2000-02-03 01:55:00')) |
+------------------------------------------------------+
| 2000-02-03 01:55:00                                  |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(unix_timestamp('2000-02-03 01:05:00'));
+------------------------------------------------------+
| from_unixtime(unix_timestamp('2000-02-03 01:05:00')) |
+------------------------------------------------------+
| 2000-02-03 01:05:00                                  |
+------------------------------------------------------+

Regards,
Monty
Thread
unix_timestamp() incorrect in some timezones.jon6 Feb
  • unix_timestamp() incorrect in some timezones.Michael Widenius13 Feb