Daylight Saving Time and System Time Zone in MySQL

daylight savings time in MySQLMarch 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).


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:

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

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

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:

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.

Share this post

Leave a Reply