Bug #95219 Incorrect FROM_UNIXTIME value during daylight savings adjustment hour
Submitted: 2 May 8:05 Modified: 21 Jun 18:31
Reporter: Mind mindi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:8.0.16 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: daylight savings, timestamp

[2 May 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 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 8:19] Bogdan Kecman
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 8:21] Bogdan Kecman
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 8:22] Bogdan Kecman
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 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 13:37] Bogdan Kecman
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 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 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 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 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 10:37] Bogdan Kecman
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 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 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