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> 
  • 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! – Joe Hopfgartner Jan 16 '17 at 0:31
  • Please SHOW VARIABLES LIKE '%zone%'; – Rick James Jan 16 '17 at 4:49
  • 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 . – Joe Hopfgartner Jan 16 '17 at 12:32
  • I suggest you flag your SO question for migration here (then we can merge them). No need for 2 questions. – ypercubeᵀᴹ Jan 16 '17 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.