Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

Question

I have debugged this a lot and finally managed to reproduce it in the mysql shell.

I am storing timestamps in a mysql database with the field type timestamp. I use FROM_UNIXTIME() to update them from my script, and I use UNIX_TIMESTAMP() when selecting them.

When I don't set a time zone in the connection using SET time_zone = it works fine. But when I do set a time zone, the following happens:

UNIX_TIMESTAMP() is still giving the correct result.
UPDATE table SET field = FROM_UNIXTIME(..) sets the wrong value in the DB.
The wrong value in that is set, does not correspond to the offset between the server time zone and the connection time zone. Server timezone is Asia/Bangkok (UTC + 7) and the connection time zone is Europe/Berlin (UTC + 1). However the value is stored with 1 hour difference, not 6 hours.
When reading the value again, I get the wrong value.

I know that it is FROM_UNIXTIME() that is not working, because when I open another connection without a connection specific timezone, I see the wrong value, until I update it again.

The fact that it is 1 hour difference lets me think that it may be a daylight saving time issue. Because Berlin has daylight saving time and Bangkok doas not (as far as I know).

This is an unmodified log of the mysql shell where I reproduced this behaviour.

The server time zone is Asia/Bangkok (CIT)

$ mysql -uroot -p timezonetest
Enter password: 
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 530
Server version: 5.7.16-0ubuntu0.16.10.1 (Ubuntu)

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> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| ICT                |
+--------------------+
1 row in set (0.00 sec)

mysql> describe test;
+------------+-----------+------+-----+-------------------+-----------------------------+
| Field      | Type      | Null | Key | Default           | Extra                       |
+------------+-----------+------+-----+-------------------+-----------------------------+
| payment_id | int(11)   | NO   | PRI | NULL              | auto_increment              |
| begins_at  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 08:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(begins_at) from test where payment_id = 338840;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382836533 |
+---------------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'CET';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(begins_at) from test where payment_id = 338840;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382836533 |
+---------------------------+
1 row in set (0.00 sec)

mysql> update test set begins_at = from_unixtime(1382836533) where payment_id = 338840;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select unix_timestamp(begins_at) from test where payment_id = 338840;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql>

Another log:

    mysql> describe test;
+------------+-----------+------+-----+-------------------+-----------------------------+
| Field      | Type      | Null | Key | Default           | Extra                       |
+------------+-----------+------+-----+-------------------+-----------------------------+
| payment_id | int(11)   | NO   | PRI | NULL              | auto_increment              |
| begins_at  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'Europe/Berlin';
Query OK, 0 rows affected (0.00 sec)

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> update test set begins_at = from_unixtime(1382836533);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'Asia/Bangkok';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 07:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> update test set begins_at = from_unixtime(1382836533);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382836533 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 08:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql>

Your clients (consumers of the database) should be passing in timestamps which already reflect their difference from the point in time represented by the server. You should not be messing around with the server timezone. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 15, 2017 at 23:49
@TimBiegeleisen The whole point of using from_unixtime()/unix_timestamp() and using only timestamps to pass data back and forth is not to mess with the time zones. Those functions should bei timezone resistant. It should be impossible to do somethign wrong whatever time zones are set, because timestamps have no time zone so it is imipossible to supply wrong data. — Joe Hopfgartner
– Joe Hopfgartner, Commented Jan 15, 2017 at 23:52
You are overlooking the double-conversion that occurs when your session time zone (or the system time zone if the session time zone is not set) is not UTC. — Michael - sqlbot
– Michael - sqlbot, Commented Jan 16, 2017 at 2:02

Michael - sqlbot · Accepted Answer · 2017-01-16 02:01:03Z

MySQL is behaving correctly – your test is invalid.

If you round-trip through a time zone with DST, you will not have a lossless conversion if you hit a transition. The timestamp in question occurs during a DST transition in "CET" and "Europe/Berlin".

There are two wall clock times in Asia/Bangkok that correspond to a single wall clock time in Europe/Berlin.

mysql> SELECT CONVERT_TZ('2013-10-27 08:15:33','Asia/Bangkok','Europe/Berlin');
+------------------------------------------------------------------+
| CONVERT_TZ('2013-10-27 08:15:33','Asia/Bangkok','Europe/Berlin') |
+------------------------------------------------------------------+
| 2013-10-27 02:15:33                                              |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2013-10-27 07:15:33','Asia/Bangkok','Europe/Berlin');
+------------------------------------------------------------------+
| CONVERT_TZ('2013-10-27 07:15:33','Asia/Bangkok','Europe/Berlin') |
+------------------------------------------------------------------+
| 2013-10-27 02:15:33                                              |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

Check this with a conversion to UTC...

mysql> select convert_tz('2013-10-27 02:59:59','Europe/Berlin','UTC');
+---------------------------------------------------------+
| convert_tz('2013-10-27 02:59:59','Europe/Berlin','UTC') |
+---------------------------------------------------------+
| 2013-10-27 00:59:59                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Two seconds later...

mysql> select convert_tz('2013-10-27 03:01:01','Europe/Berlin','UTC');
+---------------------------------------------------------+
| convert_tz('2013-10-27 03:01:01','Europe/Berlin','UTC') |
+---------------------------------------------------------+
| 2013-10-27 02:01:01                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

...it is one hour and two seconds later.

Or, flip it around.

mysql> SET @@time_zone = 'CET';

mysql> SELECT FROM_UNIXTIME(1382825733) AS zero,  
              FROM_UNIXTIME(1382825733 + 3600) AS one, 
              FROM_UNIXTIME(1382825733 + 3600 + 3600) as two, 
              FROM_UNIXTIME(1382825733 + 3600 + 3600 + 3600) as three,
              FROM_UNIXTIME(1382825733 + 3600 + 3600 + 3600 + 3600) as four;
+---------------------+---------------------+---------------------+---------------------+---------------------+
| zero                | one                 | two                 | three               | four                |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2013-10-27 00:15:33 | 2013-10-27 01:15:33 | 2013-10-27 02:15:33 | 2013-10-27 02:15:33 | 2013-10-27 03:15:33 |
+---------------------+---------------------+---------------------+---------------------+---------------------+
                                                         ^^ ... wait, what? .. ^^
1 row in set (0.00 sec)

If you do a timezone conversion on an ambiguous value during the transition hour, the conversion is not lossless.

Manipulation of timestamps needs to be UTC end-to-end. Using FROM_UNIXTIME() or UNIX_TIMESTAMP() works with native UTC values on one side or the other, but the value is still converted to and from your session time zone (or the server time zone if the session time zone is not set) on the other side -- on the way to or from being a value in a TIMESTAMP column (which is actually stored as UTC, and converted to/from your session time zone).

This is one reason why your server clock should always use UTC.

Thanks so much. So essentially from_unixtime takes the timestamp, converts it to a datetime expression that is ambigous, and when it gets converted again to be stored, the wrong value is used? So long story short, updating a timestamp field from a timestamp can only be done if the connection time zone is utc? It does not help if the server time zone is UTC if the result of from_unixtime is ambigous in the connection time zone? — Joe Hopfgartner
– Joe Hopfgartner, Commented Jan 16, 2017 at 12:54
Correct, it is the session (connection) time zone that matters most directly, because, when set, it overrides the system zone. FROM_UNIXTIME() converts the integer epoch time and converts it to a datetime literal in the session time zone. Insert/update to a TIMESTAMP then converts that datetime literal from the session time one back to UTC, which is how timestamps are stored in MySQL. — Michael - sqlbot
– Michael - sqlbot, Commented Jan 17, 2017 at 0:11
If your session is in a time zone which has transitions, this double-conversion is what creates the ambiguity -- there is an entire hour of timestamps that is equivalent to two UTC times during the autumnal transition (out of DST)... and an entire hour of timestamps that are not equivalent to any UTC timestamp during the vernal transition (into DST). Entering will not bite you with this particular ambiguity -- the ambiguity is from the wall clock moving back one hour and encountering the same 3600 seconds again when leaving DST. — Michael - sqlbot
– Michael - sqlbot, Commented Jan 17, 2017 at 0:15
If I do "set time_zone='UTC';" in the session, but server time zone is still CET, I will be fine? — Joe Hopfgartner
– Joe Hopfgartner, Commented Jan 17, 2017 at 2:33

Stack Exchange Network

Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

1 Answer 1

Your Answer

Hot Network Questions

Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions