MySQL Bugs: #52624: UNIX_TIMESTAMP() retrieves incorrect result with timezone

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

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>

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)

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>

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

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)