Demo: how TIMESTAMP breaks time in MySQL
This walkthrough puts the problem described in the article into practice.
The exercise
You need Docker and Bash. If you cannot run the experiment, you can see the result in SQL Fiddle. That version changes session.time_zone instead of the system time zone, but demonstrates the same issue.
Start MySQL, then wait for it to accept connections:
docker run --name time_test -e MYSQL_ROOT_PASSWORD=helloworld -d mysql:5.7 && \
docker exec -i -e MYSQL_PWD=helloworld time_test bash <<< 'while ! mysql -u root -e exit 2>/dev/null; do sleep 1; echo -n .; done; echo " MySQL seems to be ready"'
Make sure the container uses UTC:
docker exec -it time_test date; \
docker exec -it time_test cat /etc/timezone; \
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root <<< "SELECT NOW();"; \
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root <<< "SHOW VARIABLES LIKE 'system_time_zone';"
Sat Sep 30 16:51:27 UTC 2017
Etc/UTC
NOW()
2017-09-30 16:51:27
Variable_name Value
system_time_zone UTC
Create a table with an integer id and a required timestamp column called ts:
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root <<< "
CREATE DATABASE test;
USE test;
CREATE TABLE timestamps (id int NOT NULL, ts timestamp NOT NULL);
"
Insert the current host time:
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root test <<< "INSERT INTO timestamps VALUES (1, '$(date +%Y-%m-%d\ %H:%M:%S)');"
Bash expands $(date +%Y-%m-%d\ %H:%M:%S) to the current host time in the format MySQL expects.
Now switch the container to a different system time zone and make sure the change took effect:
docker exec -i time_test tee /etc/timezone <<< 'Europe/Minsk'; \
docker exec -i time_test dpkg-reconfigure -f noninteractive tzdata; \
docker exec -i time_test date
Europe/Minsk
Current default time zone: 'Europe/Minsk'
Local time is now: Sat Sep 30 19:51:51 +03 2017.
Universal Time is now: Sat Sep 30 16:51:51 UTC 2017.
Sat Sep 30 19:51:51 +03 2017
The final date output shows UTC+03, confirming the change. Restart MySQL so it picks up the new system time zone:
docker restart time_test && \
docker exec -i -e MYSQL_PWD=helloworld time_test bash <<< 'while ! mysql -u root -e exit 2>/dev/null; do sleep 1; echo -n .; done; echo " MySQL seems to be ready"' && \
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root test <<< "SELECT NOW();" && \
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root test <<< "SHOW VARIABLES LIKE 'system_time_zone';"
. MySQL seems to be ready
NOW()
2017-09-30 19:52:13
Variable_name Value
system_time_zone +03
Insert another row, then inspect the result:
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root test <<< "INSERT INTO timestamps VALUES (2, '$(date +%Y-%m-%d\ %H:%M:%S)');" && \
docker exec -i -e MYSQL_PWD=helloworld time_test mysql -u root test <<< "SELECT * FROM timestamps;"
id ts
1 2017-09-30 22:51:45
2 2017-09-30 19:52:24
Only 39 seconds passed between these inserts, but the query results do not show it. For the first insert, MySQL treated the supplied value as UTC and calculated this timestamp:
<?php
$timeToInsert = new DateTime('2017-09-30T19:51:45', new DateTimeZone('UTC'));
var_dump($timeToInsert->getTimestamp());
int(1506801105)
For the second, it treated 2017-09-30 19:52:24 as a time with a +03:00 offset:
<?php
$timeToInsert = new DateTime('2017-09-30T19:52:24', new DateTimeZone('+03:00'));
var_dump($timeToInsert->getTimestamp());
int(1506790344)
When we query the table, MySQL returns both Unix timestamps in its current +03:00 time zone:
<?php
$records = [
['id' => 1, 'ts' => 1506801105],
['id' => 2, 'ts' => 1506790344],
];
echo "id\tts\n";
foreach ($records as $record) {
$dateTime = new DateTime("@{$record['ts']}");
$dateTime->setTimeZone(new DateTimeZone('+03:00'));
echo $record['id'], "\t", $dateTime->format('Y-m-d H:i:s'), "\n";
}
id ts
1 2017-09-30 22:51:45
2 2017-09-30 19:52:24
That is how values in a TIMESTAMP column end up broken when MySQL’s time zone changes by any of the methods described in the article.