Comments - DATE_ADD
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
It should probably be explicitly stated somewhere in the docs:
Time-zone conversions with CONVERT_TZ() as well as FROM_UNIXTIME() and UNIX_TIMESTAMP() will take the session timezone into account.
* BUT *
All other forms of date arithmetic will not. This must be considered in timezones that have daylight savings.
E.g. in Melbourne, Australia, daylight savings begins in 2018 at 2018-10-07 02:00:00; or rather, the time on that day goes from 1:59:59 to 3:00:00.
BUT:
SELECT '2018-10-07 01:50:00' + INTERVAL 30 MINUTEGIVES: 2018-10-07 02:20:00...the result is wrong, interpreted as a local time!
WHEREAS:
SELECT CONVERT_TZ(CONVERT_TZ('2018-10-07 01:50:00',@@time_zone,'+00:00') + INTERVAL 30 MINUTE, '+00:00', @@time_zone)GIVES: 2018-10-07 03:20:00Which is the correct local clock-time 30 minutes after 1:50am.
The moral of the story is that if you might by in a local timezone with DST and you want to measure or add/subtract fixed intervals between two times, you may want to convert to UTC (or any other fixed-offset timezone, like '+10:00') first.
Similar, related problems occur at the end of daylight savings, when the local time repeats an hour.