Daylight Saving Time and System Time Zone in MySQL

March is not only the month when a pandemic closed all borders and people had to stay home on the quarantine; it’s also the month when daylight saving time change happens. For some regions, this is not only a time change but also a switch to a different timezone. For example, New York uses EST during winter and EDT during summer. If you use the system timezone and do not restart the MySQL server or a PXC node after the switch, you may notice that the change was not implemented.

You may end up with a situation where some of your cluster nodes still use the time zone before the switch (e.g. EST) and others use the timezone after the change (e.g. EDT).

$ date
Sun Mar  8 03:03:28 EDT 2020
 
$ ./bin/mysql  -h127.0.0.1 -P3373 -uroot test
...
EDT node> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | EDT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
 
$ ./bin/mysql  -h127.0.0.1 -P3372 -uroot test
...
EST node> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | EST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

Should you worry about it?

Nope!

MySQL initiates the system_time_zone variable when it is started. Even if the variable contains stalled data, all calculations are performed correctly and temporal values already use the new timezone.

To demonstrate this, let’s look at a simple table holding timestamp values:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

First, let’s check existing data; it is the same no matter which value of the system_time_zone variable uses the node:

EDT node> select * from t1;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  2 | 2020-03-08 01:03:53 |
|  4 | 2020-03-08 01:03:54 |
|  6 | 2020-03-08 01:03:55 |
+----+---------------------+
3 rows in set (0.00 sec)
 
EST node> select * from t1;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  2 | 2020-03-08 01:03:53 |
|  4 | 2020-03-08 01:03:54 |
|  6 | 2020-03-08 01:03:55 |
+----+---------------------+
3 rows in set (0.00 sec)

If we add a new row, the valid timestamp will be inserted on both nodes:

EST node> insert into t1 values();
Query OK, 1 row affected (0.01 sec)
 
EST node> select * from t1;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  2 | 2020-03-08 01:03:53 |
|  4 | 2020-03-08 01:03:54 |
|  6 | 2020-03-08 01:03:55 |
|  8 | 2020-03-08 03:02:22 |
+----+---------------------+
4 rows in set (0.00 sec)
 
EDT node> select * from t1;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  2 | 2020-03-08 01:03:53 |
|  4 | 2020-03-08 01:03:54 |
|  6 | 2020-03-08 01:03:55 |
|  8 | 2020-03-08 03:02:22 |
+----+---------------------+
4 rows in set (0.00 sec)

As you can see, the row, inserted on the node, started before the timezone change, has the same value on both nodes.

The same happens if we insert a new row on the node, started after the time change:

EDT node> insert into t1 values();
Query OK, 1 row affected (0.01 sec)
 
EDT node> select * from t1;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  2 | 2020-03-08 01:03:53 |
|  4 | 2020-03-08 01:03:54 |
|  6 | 2020-03-08 01:03:55 |
|  8 | 2020-03-08 03:02:22 |
|  9 | 2020-03-08 03:02:32 |
+----+---------------------+
5 rows in set (0.00 sec)
 
EST node> select * from t1;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  2 | 2020-03-08 01:03:53 |
|  4 | 2020-03-08 01:03:54 |
|  6 | 2020-03-08 01:03:55 |
|  8 | 2020-03-08 03:02:22 |
|  9 | 2020-03-08 03:02:32 |
+----+---------------------+
5 rows in set (0.00 sec)

Conclusion

If you use time_zone = SYSTEM , you may notice the value of the system_time_zone variable is outdated after the daylight saving time changes happen. But you should not worry about it, because all the calculations will use the updated time.