Search code examples
mysqltimezoneconvert-tz

Why does switching the order of two time zones that are the same time in mysql convert_tz make a difference?

SELECT CONVERT_TZ('2020-06-30 23:59:59','America/Caracas','US/Eastern');

This returns '2020-07-01 00:29:59' which is strange because EST and Venezuela actually share the same time.

SELECT CONVERT_TZ('2020-06-30 23:59:59','US/Eastern','America/Caracas');

This returns'2020-06-30 23:59:59' which makes perfect sense.

Why is the first query not returning the correct time, while the second one does?

Any suggestions? Thank you!

Solution

  • The most likely explanation for the observed behavior is incorrect or outdated time_zone info.

    For Caracus, Venezuela

    From '2007-12-01' to '2016-06-01', timezone offset is UTC-04:30

    Beginning '2016-06-01', timezone offset is UTC-04:00

    We don't know whether MySQL timezone tables were loaded from the zoneinfo files on the server, or from a downloaded package.

    But either way, it's the information in the timezone tables in the mysql database that are being used by the CONVERT_TZ function.