Comments - TIMESTAMP

When using the TIMESTAMP type in timezone settings with daylight savings, you must be aware that:

• Storing data in the column will be ambiguous at the end of daylight savings, because the local-time hour gets repeated twice.

• At the start of daylight savings, when clocks are turned forwards, there's a "missing hour". Technically times in this hour aren't valid, but if you do try to store a time from this missing hour in a TIMESTAMP column and then read it back, you WON'T get out what you put in! (Contrary to what the MySQL docs state).

• If you ORDER BY your timezone column, it will order by the internal UTC representation; displayed times can therefore be out of order when daylight savings ends.

• HOWEVER, any computation will use the derived local time, even in the ORDER BY clause, so if ts is a TIMESTAMP column, "ORDER BY ts" "ORDER BY <column number>", or "ORDER BY UNIX_TIMESTAMP(ts)" give UTC-time ordering, but "ORDER BY ts+0", "ORDER BY ts + INTERVAL 1 SECOND", "ORDER BY CAST(ts AS datetime)" will be ordered by local time, which may result in a different row ordering!

• Similarly, comparisons between timestamp values, i.e. ts1<ts2 etc are always carried out using local-time values. So the result of this comparison can CHANGE depending on the timezone you set, if the values are on different sides of the turn-the-clocks-back point: IT IS NOT A COMPARISON BETWEEN UTC TIMESTAMPS!!! To do that, compare UNIX_TIMESTAMP(ts1)<UNIX_TIMESTAMP(ts2)

These problems can be largely obviated by setting the server timezone to UTC (temporarily, if you prefer), i.e.:

    SET @oldTZ := @@time_zone;
    SET @@time_zone := '+00:00';
    #
    # Do something with timestamps here; it'll work reliably[*] as everything is in UTC
    #
    SET @@time_zone := @oldTZ;

[*] Actually, not entirely true, as there may also occasionally be leap-seconds, represented by hh:59:59 repeating twice. So events with these timestamps may be more than one second apart, and there isn't a 1-1 correspondence between actual seconds and displayed seconds; and when storing decimal seconds (milliseconds, etc), once again all the local-times-are-out-of order issues can arise (even in the UTC timezone). See the following page in the MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/time-zone-leap-seconds.html