Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Do
-
1.0.8
-
Debian 8.7 (Jessie)
Description
Passing a field vs passing literal values (field value same as literal) to from_unixtime() function, produce different results, depending on the time_zone variables.
|
Table structure |
MariaDB [testdb]> show create table testtbl\G |
*************************** 1. row ***************************
|
Table: testtbl |
Create Table: CREATE TABLE `testtbl` ( |
`unix_ts_col` bigint(20) DEFAULT NULL |
) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
|
Table data |
MariaDB [testdb]> select * from testtbl; |
+-------------+ |
| unix_ts_col |
|
+-------------+ |
| 1493304629 |
|
+-------------+ |
|
Env 01, system: CEST, global: SYSTEM, session: SYSTEM |
MariaDB [testdb]> show global variables like '%time_zone'; |
+------------------+--------+ |
| Variable_name | Value |
|
+------------------+--------+ |
| system_time_zone | CEST |
|
| time_zone | SYSTEM |
|
+------------------+--------+ |
|
|
MariaDB [testdb]> show variables like '%time_zone'; |
+------------------+--------+ |
| Variable_name | Value |
|
+------------------+--------+ |
| system_time_zone | CEST |
|
| time_zone | SYSTEM |
|
+------------------+--------+ |
|
|
MariaDB [testdb]> select from_unixtime(unix_ts_col), from_unixtime(1493304629) from testtbl; |
+----------------------------+---------------------------+ |
| from_unixtime(unix_ts_col) | from_unixtime(1493304629) |
|
+----------------------------+---------------------------+ |
| 2017-04-27 16:50:29 | 2017-04-27 16:50:29 |
|
+----------------------------+---------------------------+ |
|
Env 02, system: CEST, global: SYSTEM, session: america/new_york |
MariaDB [testdb]> show global variables like '%time_zone'; |
+------------------+--------+ |
| Variable_name | Value |
|
+------------------+--------+ |
| system_time_zone | CEST |
|
| time_zone | SYSTEM |
|
+------------------+--------+ |
|
|
MariaDB [testdb]> show variables like '%time_zone'; |
+------------------+------------------+ |
| Variable_name | Value |
|
+------------------+------------------+ |
| system_time_zone | CEST |
|
| time_zone | america/new_york |
|
+------------------+------------------+ |
|
|
MariaDB [testdb]> select from_unixtime(unix_ts_col), from_unixtime(1493304629) from testtbl; |
+----------------------------+---------------------------+ |
| from_unixtime(unix_ts_col) | from_unixtime(1493304629) |
|
+----------------------------+---------------------------+ |
| 2017-04-27 16:50:29 | 2017-04-27 10:50:29 |
|
+----------------------------+---------------------------+ |
|
Env 03, system: CEST, global: america/new_york, session: america/new_york |
MariaDB [testdb]> show global variables like '%time_zone'; |
+------------------+------------------+ |
| Variable_name | Value |
|
+------------------+------------------+ |
| system_time_zone | CEST |
|
| time_zone | america/new_york |
|
+------------------+------------------+ |
|
|
MariaDB [testdb]> show variables like '%time_zone'; |
+------------------+------------------+ |
| Variable_name | Value |
|
+------------------+------------------+ |
| system_time_zone | CEST |
|
| time_zone | america/new_york |
|
+------------------+------------------+ |
|
|
MariaDB [testdb]> select from_unixtime(unix_ts_col), from_unixtime(1493304629) from testtbl; |
+----------------------------+---------------------------+ |
| from_unixtime(unix_ts_col) | from_unixtime(1493304629) |
|
+----------------------------+---------------------------+ |
| 2017-04-27 16:50:29 | 2017-04-27 10:50:29 |
|
+----------------------------+---------------------------+ |
This behavior is not witnessed in mysql 5.5/mariadb 10.0/10.1.
Expected behavior is the possibility to set the session time_zone variable and filter on fields containing epoch times, using a variable time_zone.