1

The timestamps 1382836533 and 1382832933 correspond to the same wall clock time 2013-10-27 02:15:33 in cet timezone (because of daylight savings time).

Therefore the conversion with FROM_UNIXTIME is lossy.

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

mysql> select unix_timestamp(from_unixtime(1382836533));
+-------------------------------------------+
| unix_timestamp(from_unixtime(1382836533)) |
+-------------------------------------------+
|                                1382832933 |
+-------------------------------------------+
1 row in set (0.00 sec)

I have a field of type timestamp in my database. If I want to update it, with my time zone settings, I end up writing the wrong value to the database:

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) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> 

The problem does not occur when I manually set the connection time zone to `UTC.

It does occur when I manually set the connection time zone to CET.

But it does not occur when I do nothing. Which is strange because the server and client have both CET as their default time zone.

So the question is: Why does the problem not occur when I don't manually set the timezone? And what precise combinations of server and connection time zones produce this problems and which do not?

The log below reflects the behaviour.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CET    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

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

mysql> select from_unixtime(1382836533);
+---------------------------+
| from_unixtime(1382836533) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1382832933);
+---------------------------+
| from_unixtime(1382832933) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

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

mysql> select unix_timestamp(from_unixtime(1382836533));
+-------------------------------------------+
| unix_timestamp(from_unixtime(1382836533)) |
+-------------------------------------------+
|                                1382832933 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1382832933);
+---------------------------+
| from_unixtime(1382832933) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1382836533);
+---------------------------+
| from_unixtime(1382836533) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

mysql> 
6
  • I just realised that the problem seems to only appear with specific timestamps? 1382836533 is such a time stamp, but another works just fine. WTF! Commented Jan 16, 2017 at 0:31
  • Please SHOW VARIABLES LIKE '%zone%';
    – Rick James
    Commented Jan 16, 2017 at 4:49
  • Essentially a cross-post from Stack Overflow. Commented Jan 16, 2017 at 10:14
  • 1
    I had the problems in a PHP script and could not figure it out, thats why i was on stack overflow. I first thought it was a PHP or PDO issue. Then I realised it was a pure DB issue . Commented Jan 16, 2017 at 12:32
  • I suggest you flag your SO question for migration here (then we can merge them). No need for 2 questions. Commented Jan 16, 2017 at 18:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.