| Bug #80863 | Handling of DST changes | ||
|---|---|---|---|
| Submitted: | 27 Mar 2016 9:38 | Modified: | 17 Nov 2016 15:37 |
| Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7.11, 5.7.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | convert_tz, DST, timezone, tz, tzdata | ||
[27 Mar 2016 9:38]
Daniël van Eeden
[28 Mar 2016 7:42]
Daniël van Eeden
Another situation with unexpected results and no warning. mysql> SELECT d, DATE_SUB(d,INTERVAL 5 MINUTE), FROM_UNIXTIME((UNIX_TIMESTAMP(d)-(5*60))) FROM (SELECT '2016-03-27 01:03:06' d UNION ALL SELECT '2016-03-27 02:03:06' d UNION ALL SELECT '2016-03-27 03:03:06') a; +---------------------+-------------------------------+-------------------------------------------+ | d | DATE_SUB(d,INTERVAL 5 MINUTE) | FROM_UNIXTIME((UNIX_TIMESTAMP(d)-(5*60))) | +---------------------+-------------------------------+-------------------------------------------+ | 2016-03-27 01:03:06 | 2016-03-27 00:58:06 | 2016-03-27 00:58:06.000000 | | 2016-03-27 02:03:06 | 2016-03-27 01:58:06 | 2016-03-27 01:55:00.000000 | | 2016-03-27 03:03:06 | 2016-03-27 02:58:06 | 2016-03-27 01:58:06.000000 | +---------------------+-------------------------------+-------------------------------------------+ 3 rows in set (0.01 sec)
[28 Mar 2016 8:19]
Daniël van Eeden
For the DATE_SUB() issue:
If timezone data is loaded this seems to do the trick:
SET @old_tz := @@time_zone;
SET time_zone = 'UTC';
SELECT CONVERT_TZ(CONVERT_TZ('2016-03-27
03:03:06','Europe/Amsterdam','UTC') - INTERVAL 5
MINUTE,'UTC','Europe/Amsterdam');
SET time_zone = @old_tz;
Without timzeone date this seems to work:
CREATE FUNCTION date_sub_dst (d datetime, s int)
RETURNS datetime DETERMINISTIC
RETURN FROM_UNIXTIME(UNIX_TIMESTAMP(d) - s);
SELECT date_sub_dst('2016-03-27 03:03:06',5*60);
Maybe DATE_SUB() should have a third parameter to specify the timezone? (or datetime datatype which supports timezones)
Also what is the datatype of 'INTERVAL 5 MINUTE'? How could I create a procedure/function which supports that as an argument?
[13 Apr 2016 13:48]
MySQL Verification Team
Hi! Thank you for your bug report. However, I do not see a bug here. Many of the temporal functions depend entirely on the proper settings of the timezone. It is set as a reserved global variable and is not meant to be used as an additional parameter. That is how the system is designed and documented. If you feel that this is not documented enough, please point us to the chapter(s) in our latest 5.7 manual where you do not see this design properly documented.
[14 May 2016 1: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".
[17 Nov 2016 10:47]
Simon Mudd
Related: bug#83852
[17 Nov 2016 12:46]
Daniël van Eeden
What I consider a bug is this: CONVERT_TZ(d,'CET','CET') converts from CET to CET but the input and output are different.
[17 Nov 2016 15:07]
MySQL Verification Team
I do not see any bug here:
version: '5.7.13-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
./client/mysql test -e "SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','GMT')"
+-----------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','GMT') |
+-----------------------------------------------+
| 2004-01-01 12:00:00 |
+-----------------------------------------------+
./client/mysql test -e "SELECT CONVERT_TZ('2014-01-01 12:35:00','CET','CET')"
+-----------------------------------------------+
| CONVERT_TZ('2014-01-01 12:35:00','CET','CET') |
+-----------------------------------------------+
| 2014-01-01 12:35:00 |
+-----------------------------------------------+
./client/mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.13-debug Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET @d='2016-11-17';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONVERT_TZ(@d,'CET','CET');
+----------------------------+
| CONVERT_TZ(@d,'CET','CET') |
+----------------------------+
| 2016-11-17 00:00:00 |
+----------------------------+
Hence, not a bug !!!!
[17 Nov 2016 15:22]
Daniël van Eeden
mysql> SELECT CONVERT_TZ('2016-03-27 02:03:06','CET','CET');
+-----------------------------------------------+
| CONVERT_TZ('2016-03-27 02:03:06','CET','CET') |
+-----------------------------------------------+
| 2016-03-27 03:00:00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
It should either tell me that the input (or operation) is wrong or return the same timestamp in return.
[17 Nov 2016 15:37]
MySQL Verification Team
I managed to repeat it . mysql> SELECT CONVERT_TZ(@d,'CET','CET'); +----------------------------+ | CONVERT_TZ(@d,'CET','CET') | +----------------------------+ | 2016-11-17 00:00:00 | +----------------------------+ 1 row in set (0.00 sec) Fully verified.
[17 Nov 2016 15:42]
MySQL Verification Team
Sorry, wrong copy / paste :
mysql> SELECT CONVERT_TZ('2016-03-27 02:03:06','CET','CET');
+-----------------------------------------------+
| CONVERT_TZ('2016-03-27 02:03:06','CET','CET') |
+-----------------------------------------------+
| 2016-03-27 03:00:00 |
+-----------------------------------------------+
