[MDEV-7928] MySQL time is not aware of timezone settings - Jira

Vincent Kéravec created issue - 2015-04-07 16:11

Vincent Kéravec made changes - 2015-04-07 16:11

Field	Original Value	New Value
Description	MySQL is not timezone aware while calculating time. Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832 {{MariaDB [(none)]> select @@session.time_zone; +---------------------+ \| @@session.time_zone \| +---------------------+ \| Europe/Oslo \| +---------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')); +------------------------------------------------------+ \| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) \| +------------------------------------------------------+ \| 2015-03-29 03:00:00 \| +------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME); +-----------------------------------------+ \| CAST('2015-03-29 02:00:00' as DATETIME) \| +-----------------------------------------+ \| 2015-03-29 02:00:00 \| +-----------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR; +---------------------------------------------------------+ \| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR \| +---------------------------------------------------------+ \| 2015-03-29 02:00:01 \| +---------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600); +-----------------------------------------------------------+ \| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) \| +-----------------------------------------------------------+ \| 2015-03-29 03:00:01 \| +-----------------------------------------------------------+ 1 row in set (0.00 sec) }}	MySQL is not timezone aware while calculating time. Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832 {{ MariaDB [(none)]> select @@session.time_zone; +---------------------+ \| @@session.time_zone \| +---------------------+ \| Europe/Oslo \| +---------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')); +------------------------------------------------------+ \| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) \| +------------------------------------------------------+ \| 2015-03-29 03:00:00 \| +------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME); +-----------------------------------------+ \| CAST('2015-03-29 02:00:00' as DATETIME) \| +-----------------------------------------+ \| 2015-03-29 02:00:00 \| +-----------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR; +---------------------------------------------------------+ \| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR \| +---------------------------------------------------------+ \| 2015-03-29 02:00:01 \| +---------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600); +-----------------------------------------------------------+ \| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) \| +-----------------------------------------------------------+ \| 2015-03-29 03:00:01 \| +-----------------------------------------------------------+ 1 row in set (0.00 sec) }}

Vincent Kéravec made changes - 2015-04-07 16:12

Description

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832

{{
MariaDB [(none)]> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| Europe/Oslo |
+---------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
+------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
+------------------------------------------------------+
| 2015-03-29 03:00:00 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
+-----------------------------------------+
| CAST('2015-03-29 02:00:00' as DATETIME) |
+-----------------------------------------+
| 2015-03-29 02:00:00 |
+-----------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
+---------------------------------------------------------+
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
+---------------------------------------------------------+
| 2015-03-29 02:00:01 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
+-----------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
+-----------------------------------------------------------+
| 2015-03-29 03:00:01 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

}}

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832

{{
MariaDB [(none)]> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| Europe/Oslo |
+---------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
| 2015-03-29 03:00:00 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
| CAST('2015-03-29 02:00:00' as DATETIME) |
| 2015-03-29 02:00:00 |
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
| 2015-03-29 02:00:01 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
| 2015-03-29 03:00:01 |
1 row in set (0.00 sec)

}}

Vincent Kéravec made changes - 2015-04-07 16:12

Description

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832

{{
MariaDB [(none)]> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| Europe/Oslo |
+---------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
| 2015-03-29 03:00:00 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
| CAST('2015-03-29 02:00:00' as DATETIME) |
| 2015-03-29 02:00:00 |
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
| 2015-03-29 02:00:01 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
| 2015-03-29 03:00:01 |
1 row in set (0.00 sec)

}}

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832

{{
MariaDB [(none)]> select @@session.time_zone;
| @@session.time_zone |
| Europe/Oslo |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
| 2015-03-29 03:00:00 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
| CAST('2015-03-29 02:00:00' as DATETIME) |
| 2015-03-29 02:00:00 |
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
| 2015-03-29 02:00:01 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
| 2015-03-29 03:00:01 |
1 row in set (0.00 sec)

}}

Vincent Kéravec made changes - 2015-04-07 16:12

Description

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832

{{
MariaDB [(none)]> select @@session.time_zone;
| @@session.time_zone |
| Europe/Oslo |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
| 2015-03-29 03:00:00 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
| CAST('2015-03-29 02:00:00' as DATETIME) |
| 2015-03-29 02:00:00 |
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
| 2015-03-29 02:00:01 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
| 2015-03-29 03:00:01 |
1 row in set (0.00 sec)

}}

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832

MariaDB [(none)]> select @@session.time_zone;
| @@session.time_zone |
| Europe/Oslo |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
| 2015-03-29 03:00:00 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
| CAST('2015-03-29 02:00:00' as DATETIME) |
| 2015-03-29 02:00:00 |
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
| 2015-03-29 02:00:01 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
| 2015-03-29 03:00:01 |
1 row in set (0.00 sec)

Sergei Golubchik made changes - 2015-04-07 17:05

Description

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832

MariaDB [(none)]> select @@session.time_zone;
| @@session.time_zone |
| Europe/Oslo |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
| 2015-03-29 03:00:00 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
| CAST('2015-03-29 02:00:00' as DATETIME) |
| 2015-03-29 02:00:00 |
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
| 2015-03-29 02:00:01 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
| 2015-03-29 03:00:01 |
1 row in set (0.00 sec)

MySQL is not timezone aware while calculating time.
Link to a related MySQL bug http://bugs.mysql.com/bug.php?id=68832
{noformat}
MariaDB [(none)]> select @@session.time_zone;
| @@session.time_zone |
| Europe/Oslo |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00'));
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 02:00:00')) |
| 2015-03-29 03:00:00 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT CAST('2015-03-29 02:00:00' as DATETIME);
| CAST('2015-03-29 02:00:00' as DATETIME) |
| 2015-03-29 02:00:00 |
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR;
| CAST('2015-03-29 01:00:01' as DATETIME)+INTERVAL 1 HOUR |
| 2015-03-29 02:00:01 |
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600);
| FROM_UNIXTIME(UNIX_TIMESTAMP('2015-03-29 01:00:01')+3600) |
| 2015-03-29 03:00:01 |
1 row in set (0.00 sec)
{noformat}

Sergei Golubchik made changes - 2015-04-07 17:06

Labels

upstream

Sergei Golubchik made changes - 2015-04-07 17:07

Remote Link

This issue links to "Bug #68832 Daylight Saving time and interval (Web Link)" [ 22903 ]

Sergei Golubchik made changes - 2015-04-07 17:07

Fix Version/s

10.0 [ 16000 ]

Sergei Golubchik made changes - 2015-04-07 17:07

Affects Version/s		10.0 [ 16000 ]
Affects Version/s		5.5 [ 15800 ]
Affects Version/s	10.0.17 [ 18300 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 60382 ]

MariaDB v3 [ 64962 ]

Alexander Barkov made changes - 2017-04-17 05:13

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Alexander Barkov made changes - 2017-04-17 05:14

Labels

upstream

datatype upstream

Alexander Barkov made changes - 2019-11-15 13:07

Component/s		Data types [ 13906 ]
Component/s		Temporal Types [ 11000 ]

Julien Fritsch made changes - 2019-11-18 11:41

Epic Link

MDEV-21071 [ 80504 ]

Julien Fritsch made changes - 2019-11-18 12:00

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Sergei Golubchik made changes - 2021-12-06 21:32

Workflow

MariaDB v3 [ 64962 ]

MariaDB v4 [ 139801 ]

Sergei Golubchik made changes - 2022-09-08 09:53

Fix Version/s

10.0 [ 16000 ]

MariaDB Server

MySQL time is not aware of timezone settings

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration