Bug #90109 @@system_time_zone convertion returns null when using EDT

• Submitted: 16 Mar 2018 12:39
• Modified: 20 Mar 2018 12:51
• Reporter: Adamo Tonete
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.21
• OS: Debian (Ubuntu 16.04 LTS)
• CPU Architecture: Any
• Tags: data convertion, timezone

[16 Mar 2018 12:39] Adamo Tonete

Description:
When using US/Eastern or EST5EDT as system timezone the variable system_time_zone reports or EDT or EST.

For EST it does work but for EDT there is no entry in the tzlocal package and for this reason the tz_convet function returns null 

How to repeat:
uname -a
Linux lnx1 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

System configured to use 'US/Eastern'

timedatectl
      Local time: Sex 2018-03-16 08:20:10 EDT
  Universal time: Sex 2018-03-16 12:20:10 UTC
        RTC time: Sex 2018-03-16 12:20:10
       Time zone: US/Eastern (EDT, -0400)
 Network time on: yes
NTP synchronized: yes
 RTC in local TZ: no

Loaded timezones with:
https://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html

./5.7.21/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | ./5.7.21/bin/mysql -h 127.0.0.1 --port 5721 -u root -p  mysql

mysql

Server version: 5.7.21 MySQL Community Server (GPL)
[localhost] {msandbox} ((none)) > select @@time_zone;

+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+
1 row in set (0,00 sec)

mysql [localhost] {msandbox} ((none)) > select @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EDT                |
+--------------------+
1 row in set (0,00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-11 2:00:00', @@system_time_zone, 'UTC');
+-------------------------------------------------------------+
| CONVERT_TZ('2018-03-11 2:00:00', @@system_time_zone, 'UTC') |
+-------------------------------------------------------------+
| NULL                                                        |
+-------------------------------------------------------------+
1 row in set (0,00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-11 2:00:00', @@time_zone, 'UTC');
+------------------------------------------------------+
| CONVERT_TZ('2018-03-11 2:00:00', @@time_zone, 'UTC') |
+------------------------------------------------------+
| 2018-03-11 07:00:00                                  |
+------------------------------------------------------+
1 row in set (0,00 sec)

Possible workaround
-- create a TZ called EDT pointing to America/New_York as the US/Easten is deprecated: See more at: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

cd /usr/share/zoneinfo/
sudo ln -s America/New_York EDT

-- reload the tzs
./5.7.21/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | ./5.7.21/bin/mysql -h 127.0.0.1 --port 5721 -u root -p  mysql

-- restart the service
-- ./restart

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-11 2:00:00', @@time_zone, 'UTC');
+------------------------------------------------------+
| CONVERT_TZ('2018-03-11 2:00:00', @@time_zone, 'UTC') |
+------------------------------------------------------+
| 2018-03-11 07:00:00                                  |
+------------------------------------------------------+
1 row in set (0,01 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-11 3:00:00', @@system_time_zone, 'UTC');
+-------------------------------------------------------------+
| CONVERT_TZ('2018-03-11 2:00:00', @@system_time_zone, 'UTC') |
+-------------------------------------------------------------+
| 2018-03-11 07:00:00                                         |
+-------------------------------------------------------------+
1 row in set (0,00 sec)

Suggested fix:
Warn when starting the service that the current system timezone is deprecated.
Translate EDT/EST to America/New_York

[20 Mar 2018 12:51] MySQL Verification Team

Hello Adamo,

Thank you for the report and feedback.

Thanks,
Umesh