Bug #95219 Incorrect FROM_UNIXTIME value during daylight savings adjustment hour
Submitted: 2 May 2019 8:05 Modified: 4 Nov 2019 15:29
Reporter: Mind mindi Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:8.0.16 OS:Windows
Assigned to: CPU Architecture:Any
Tags: daylight savings, timestamp

[2 May 2019 8:05] Mind mindi
Description:
During the daylight savings hours, the timestamp values are incorrectly stored and retrieved. For example for UTC time

GMT: Saturday, 6 April 2019 15:00:00
Australia/Sydney timezone: Sunday, 7 April 2019 02:00:00 GMT+11:00 DST
select FROM_UNIXTIME(1554562800);
2019-04-07 02:00:00

GMT: Saturday, 6 April 2019 16:00:00
Australia/Sydney timezone: Sunday, 7 April 2019 02:00:00 GMT+10:00
select FROM_UNIXTIME(1554566400);
2019-04-07 02:00:00

The documentation says that TIMESTAMP format is stored in UTC format as integer and should preserve original values. However, when I call functions on these two different values, the MySql server returns the same date.

select UNIX_TIMESTAMP(FROM_UNIXTIME(1554562800));
INCORRECT result : 1554566400

select UNIX_TIMESTAMP(FROM_UNIXTIME(1554566400)); 
  CORRECT result : 1554566400

In a similar way, then I try to convert the timezone to UTC, I get the same result for the different input values.

SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_datetime`
INCORRECT result : 2019-04-06 16:00:00

SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;
INCORRECT result : 2019-04-06 16:00:00

If the TIMESTAMP value was internally stored correctly then these 2 calls should return different results.

How to repeat:
GMT: Saturday, 6 April 2019 15:00:00
Australia/Sydney timezone: Sunday, 7 April 2019 02:00:00 GMT+11:00 DST
select FROM_UNIXTIME(1554562800);
2019-04-07 02:00:00

GMT: Saturday, 6 April 2019 16:00:00
Australia/Sydney timezone: Sunday, 7 April 2019 02:00:00 GMT+10:00
select FROM_UNIXTIME(1554566400);
2019-04-07 02:00:00

select UNIX_TIMESTAMP(FROM_UNIXTIME(1554562800));
INCORRECT result : 1554566400

select UNIX_TIMESTAMP(FROM_UNIXTIME(1554566400)); 
  CORRECT result : 1554566400

SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_datetime`
INCORRECT result : 2019-04-06 16:00:00

SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;
INCORRECT result : 2019-04-06 16:00:00

Suggested fix:
The internal TIMESTAMP value should be stored as UTC INTEGER time, so that when the different values are passed to these functions they should return different results, even if the display value in the current timezone is the same.
[13 May 2019 6:49] Mind mindi
Correction to the last sample

SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_datetime`
INCORRECT result : 2019-04-06 16:00:00

SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;
  CORRECT result : 2019-04-06 16:00:00
[15 May 2019 8:19] MySQL Verification Team
5.7.25 I'm getting proper results

master [localhost:19226] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_d                                                              atetime`
    -> ;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 15:00:00 |
+---------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 16:00:00 |
+---------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > select FROM_UNIXTIME(1554562800);
+---------------------------+
| FROM_UNIXTIME(1554562800) |
+---------------------------+
| 2019-04-06 17:00:00       |
+---------------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > select FROM_UNIXTIME(1554566400);
+---------------------------+
| FROM_UNIXTIME(1554566400) |
+---------------------------+
| 2019-04-06 18:00:00       |
+---------------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > select UNIX_TIMESTAMP(FROM_UNIXTIME(1554562800));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1554562800)) |
+-------------------------------------------+
|                                1554562800 |
+-------------------------------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > select UNIX_TIMESTAMP(FROM_UNIXTIME(1554566400));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1554566400)) |
+-------------------------------------------+
|                                1554566400 |
+-------------------------------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_datetime`
    -> ;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 15:00:00 |
+---------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 16:00:00 |
+---------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > select @@version;
+------------+
| @@version  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_datetime`;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 15:00:00 |
+---------------------+
1 row in set (0.00 sec)

master [localhost:19226] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 16:00:00 |
+---------------------+
1 row in set (0.00 sec)
[15 May 2019 8:21] MySQL Verification Team
mysql [localhost:8015] {msandbox} (test) > select FROM_UNIXTIME(1554562800);
+---------------------------+
| FROM_UNIXTIME(1554562800) |
+---------------------------+
| 2019-04-06 17:00:00       |
+---------------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} (test) >
mysql [localhost:8015] {msandbox} (test) > select FROM_UNIXTIME(1554566400);
+---------------------------+
| FROM_UNIXTIME(1554566400) |
+---------------------------+
| 2019-04-06 18:00:00       |
+---------------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} (test) > select UNIX_TIMESTAMP(FROM_UNIXTIME(1554562800));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1554562800)) |
+-------------------------------------------+
|                                1554562800 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} (test) > select UNIX_TIMESTAMP(FROM_UNIXTIME(1554566400));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1554566400)) |
+-------------------------------------------+
|                                1554566400 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_datetime`
    -> ;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 15:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 16:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554562800), @@session.time_zone, '+00:00') AS `utc_datetime`
    -> ;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 15:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} (test) > SELECT CONVERT_TZ(FROM_UNIXTIME(1554566400), @@session.time_zone, '+00:00') AS `utc_datetime`;;
+---------------------+
| utc_datetime        |
+---------------------+
| 2019-04-06 16:00:00 |
+---------------------+
1 row in set (0.00 sec)

ERROR:
No query specified

mysql [localhost:8015] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)
[15 May 2019 8:22] MySQL Verification Team
Hi,

There's something in your report I'm obviously not following properly as I'm not getting the errors you do get. This looks perfectly ok to me. Are you using Oracle binaries or ?

all best
Bogdan
[4 Jun 2019 6:54] Mind mindi
Hi Bogdan, could you please try to set your server and client time to Sydney/Australia timezone. I am testing on Windows 2012 R2 server. I am consistently getting this error/behaviour on MySql 8.0.16 on multiple installations.
[4 Jun 2019 13:37] MySQL Verification Team
Hi,

I tried with different TZ settings on server and it all works flawlessly but this is all tested on linux. There's a chance windows have a bug (in windows or in our implementation) so lemme test windows too.
[5 Jun 2019 23:57] Mind mindi
Hi Bogdan, I would agree with you. It is likely to be an issue with Windows Implementation. Once you guys test it with Windows Server 2012 R2 please update me if you were able to reproduce the error. I tested it on server installs, and they all have the same behaviour.
[13 Jun 2019 12:34] Mattias Jonsson
To help with the analysis, please see bug#83852, where I use a linux server in 'Europe/Amsterdam' local time, and @@time_zone is set to 'SYSTEM', which reproduces this bug. However, please do not close that bug as a duplicate, since it also reports other issues.
[13 Jun 2019 12:41] Mattias Jonsson
Slightly related to bug#15654, but this bug should not be closed as a duplicate or documentation bug, but rather this bug is that there is no direct a way to insert values like in the example in my comment in bug#83852 while in @@time_zone = 'SYSTEM', set to Europe/Amsterdam.
[13 Jun 2019 13:20] Mattias Jonsson
Test case for mtr:
CREATE TABLE t (
  id int primary key,
  t timestamp);

SET TIME_ZONE="+00:00";
INSERT INTO t VALUES
(1, "2010-10-30 22:30:00"),
(2, "2010-10-30 23:30:00");

SELECT id, t, unix_timestamp(t) from t; 
SET TIME_ZONE="Europe/Moscow";
SELECT id, t, unix_timestamp(t) from t; 
SELECT FROM_UNIXTIME(1288477800);
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1288477800));
SELECT FROM_UNIXTIME(1288481400);
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1288481400));
INSERT INTO t VALUES (3, FROM_UNIXTIME(1288477800)), (4, FROM_UNIXTIME(1288481400));
SELECT id, t, unix_timestamp(t) from t; 
DROP t;
[19 Jun 2019 10:37] MySQL Verification Team
Verified on windows

weird behavior, I'm pushing this to be fixed asap

thanks for the report
bogdan

p.s. @Mattias, not closing, no.... I don't even thing apart from being visually about same thing that they are code wise related at all but we'll see when we start fixing :) thanks for mtr
[19 Jun 2019 11:24] Mattias Jonsson
@Bogdan, when looking into the fix, maybe also extend the usable time zones in mtr, to include a CET time zone? Especially since the Moscow one does no longer have DST changes.
[21 Jun 2019 18:31] Mind mindi
Hey Bogdan, thank for following this through and reproducing this bug. It is greatly appreciated. I stumbled upon this when my prototype app, lost 1 hour of electrical meter data because it translated 2 different hours from Australia/Sydney timezone into the same hour in the UTC time zone. Any application that is recording time series will have an issue hitting this bug.
Your hard work and dedication are quite impressive.
Best Regards,
Nenad
[4 Nov 2019 15:29] Chandan Kunal
Posted by developer:
 
This problem is well documented in manual. Following is the content from the manual which clearly describes the problem.

If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between values in a non-UTC time zone and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes such as Daylight Saving Time (DST), it is possible for UNIX_TIMESTAMP() to map two values that are distinct in a non-UTC time zone to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original values. Here is an example, using values that are distinct in the MET time zone:

mysql> SET time_zone = 'MET';
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00       |
+---------------------------+