Spring forward: the missing hour

from the Artful MySQL Tips List

By default, MySQL servers run under the SYSTEM timezone. In many locales, computers often use time zone settings that adjust to Daylight Saving Time (DST).

If your export timestamp values from a MySQL server that ignores DST to one that adjusts to it, timestamp values for the hour that begins at the time of the spring shift forward will elicit an "Incorrect datetime value" error because the DST time change makes that hour disappear, so such timestamp values---e.g., '2022-03-11-02...'---are invalid.

Remedies to choose from ...

1 If the operating system supports it, turn off DST.

2 Use a timezone setting that ignores DST, e.g., EST, or simply SET SESSION time_zone = '+0:00'.

3 Set the system timezone to GMT + some constant offset. e.g., UTC.

4 Store dates as timestamp integers.

5 Use datetime instead of timestamp columns and add a timezone constant to all values.

Last updated 9 Apr 2022

Return to the Artful MySQL Tips page