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:
None 
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
Description:
I have discovered really strange behaviour under Windows when i specify timezone as Europe/Prague then UNIX_TIMESTAMP() adds 24 seconds to result.

SET time_zone = 'Europe/Prague';
SELECT UNIX_TIMESTAMP('2010-01-01');
#output 1262300424

SET time_zone = '+01:00';
SELECT UNIX_TIMESTAMP('2010-01-01');
#output 1262300400

also other cities are affected, not only Prague :)
Under Linux the function works correctly

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 170
Server version: 5.1.45-community 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.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2010-01-01');
+------------------------------+
| UNIX_TIMESTAMP('2010-01-01') |
+------------------------------+
|                   1262300424 |
+------------------------------+
1 row in set (0.00 sec)

mysql>
[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)