Bug #52624 | UNIX_TIMESTAMP() retrieves incorrect result with timezone | ||
---|---|---|---|
Submitted: | 6 Apr 2010 16:57 | Modified: | 7 May 2010 12:02 |
Reporter: | Michal Vrchota | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: General | Severity: | S2 (Serious) |
Version: | 5.1.45 | OS: | Windows |
Assigned to: | CPU Architecture: | Any | |
Tags: | SECONDS, time_zone 24, unix_timestamp |
[6 Apr 2010 16:57]
Michal Vrchota
[7 Apr 2010 12:02]
Valeriy Kravchuk
The question is: had you downloaded and installed recent timezone tables (from http://dev.mysql.com/downloads/timezones.html)? Look: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 100 Server version: 5.1.43-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET time_zone = 'Europe/Prague'; Query OK, 0 rows affected (0.05 sec) mysql> SELECT UNIX_TIMESTAMP('2010-01-01'); +------------------------------+ | UNIX_TIMESTAMP('2010-01-01') | +------------------------------+ | 1262300400 | +------------------------------+ 1 row in set (0.02 sec)
[5 May 2010 17:16]
Paul Norman
I have installed the latest MySql version (5.1.46) and downloaded / installed recent timezone tables (from http://dev.mysql.com/downloads/timezones.html), but still I get this bug for every timezone... C:\mysql\bin>mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 100 Server version: 5.1.46-community MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET time_zone = 'Europe/Prague'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2010-01-01'); +------------------------------+ | UNIX_TIMESTAMP('2010-01-01') | +------------------------------+ | 1262300424 | +------------------------------+ 1 row in set (0.00 sec) mysql> SET time_zone = '+01:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2010-01-01'); +------------------------------+ | UNIX_TIMESTAMP('2010-01-01') | +------------------------------+ | 1262300400 | +------------------------------+ 1 row in set (0.00 sec) mysql>
[7 May 2010 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[19 Oct 2016 2:14]
Jesper wisborg Krogh
Posted by developer: If anyone still encounters this,then be aware that on http://dev.mysql.com/downloads/timezones.html there are two options for downloading the time zone data: * POSIX standard * Non POSIX with leap seconds (Remember that a restart is required after reloading the time zone tables - see Bug 11747218). With the POSIX standard download, the conversions work as expected: mysql> SELECT VERSION(); +-------------------------------------------+ | VERSION() | +-------------------------------------------+ | 5.7.16-enterprise-commercial-advanced-log | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> SET time_zone = 'Australia/Sydney'; Query OK, 0 rows affected (0.04 sec) mysql> SELECT UNIX_TIMESTAMP('2016-10-19 13:00:00'); +---------------------------------------+ | UNIX_TIMESTAMP('2016-10-19 13:00:00') | +---------------------------------------+ | 1476842400 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> SET time_zone = '+11:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2016-10-19 13:00:00'); +---------------------------------------+ | UNIX_TIMESTAMP('2016-10-19 13:00:00') | +---------------------------------------+ | 1476842400 | +---------------------------------------+ 1 row in set (0.00 sec) Where as with Non POSIX with leap seconds: mysql> SET time_zone = 'Australia/Sydney'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT UNIX_TIMESTAMP('2016-10-19 13:00:00'); +---------------------------------------+ | UNIX_TIMESTAMP('2016-10-19 13:00:00') | +---------------------------------------+ | 1476842426 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> SET time_zone = '+11:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2016-10-19 13:00:00'); +---------------------------------------+ | UNIX_TIMESTAMP('2016-10-19 13:00:00') | +---------------------------------------+ | 1476842400 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 1476842426 - 1476842400; +-------------------------+ | 1476842426 - 1476842400 | +-------------------------+ | 26 | +-------------------------+ 1 row in set (0.01 sec)