CONVERT_TZ not working properly during winter/summ

Question

In Paris (timezone Europe/Paris) the timezone offset change on 2016-03-27 at 2:00am. Timezone is before at UTC+1 and after at UTC+2.

Mysql function CONVERT_TZ not work if you give a time during the change : between 2016-03-27 02:00:01 and 2016-03-27 02:59:59

Example :

SELECT CONVERT_TZ('2016-03-27 02:18:29', 'Europe/Paris', "UTC" ) === '2016-03-27 01:00:00'

And funny : SELECT CONVERT_TZ('2016-03-27 01:00:00', 'UTC', "Europe/Paris" ) === '2016-03-27 03:00:00'

I have to convert a mysql database with date in many timezone to a new database with all date directly in UTC timezone (safe), with this issue some date will be wrong :(

Answer 1

Given an "invalid" local time, MySQL's CONVERT_TZ function advances it to the next valid local time before converting it. Since your 2:18:29 value really does not occur on that date in that time zone, it is advanced to 3:00:00 local time, then converted correctly to 1:00:00 UTC.

In the fall, when DST ends in Paris on October 30th 2016, 2:18:29 will occur twice. You'll find that MySQL's CONVERT_TZ function chooses the second of two occurrences.

This behavior is consistent, and thus saying it doesn't work is incorrect. It works, it just may not be the behavior you would prefer.

Personally, I think in the spring it would be better to skip ahead to 3:18:39 (which converts to 1:18:29 UTC). I also think in the fall it's better to use the first of the two occurrences. I think that behavior accommodates a larger number of real-world scenarios, but that's just my opinion.

Besides, why do you have invalid times in your data to begin with? If they are recorded from real events, the time will be skipped on the clock and thus not end up in your database. So usually, one only has to deal with this when dealing with calculation of time, such as a recurring schedule.

Regarding your second example, there's nothing wrong there. 1:00 UTC would indeed be 3:00 in Paris when the time zone offset is UTC+2.