|
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.
|
