Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 11.8.8
-
Can result in unexpected behaviour
Description
`FROM_UNIXTIME` is documented to use the system's formatter in certain cases, but it somehow manages to produce wrong results where other programs using the system's formatter do not.
If your session time zone is set to SYSTEM (the default), FROM_UNIXTIME() will call the OS function to convert the data using the system time zone. At least on Linux, the corresponding function (localtime_r)...
{{
MariaDB [test]> SELECT @@system_time_zone, @@global.time_zone, @@session.time_zone, FROM_UNIXTIME(1603584000, '%Y-%m-%d %H:%i:%s %z'), FROM_UNIXTIME(1603587600, '%Y-%m-%d %H:%i:%s %z');
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| @@system_time_zone | @@global.time_zone | @@session.time_zone | FROM_UNIXTIME(1603584000, '%Y-%m-%d %H:%i:%s %z') | FROM_UNIXTIME(1603587600, '%Y-%m-%d %H:%i:%s %z') |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| CEST | SYSTEM | SYSTEM | 2020-10-25 02:00:00 +0100 | 2020-10-25 02:00:00 +0100 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0,000 sec)
}}
Notice the 4th column show the wrong time for 1603584000.
The system produces the correct representations for the same epochs:
{{
$ date d @1603584000 '+%Y%m-%d %H:%M:%S %z'
2020-10-25 02:00:00 +0200
$ date d @1603587600 '+%Y%m-%d %H:%M:%S %z'
2020-10-25 02:00:00 +0100
}}
