Mysql – Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

MySQLtimestamptimezone

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:

  1. UNIX_TIMESTAMP() is still giving the correct result.
  2. UPDATE table SET field = FROM_UNIXTIME(..) sets the wrong value in the DB.
  3. 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.
  4. 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>

Best Answer

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.

Related Question