1
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!

user6655061
  • 92
  • 1
  • 7
  • daylight savings time. am/car doesn't observe DST while us/eastern does. – Marc B Sep 29 '16 at 17:42
  • So then should it not show the time difference in the second statement as well? – user6655061 Sep 29 '16 at 18:17
  • @MarcB: In 2016, Caracas, Venezuela observed a time offset of one half hour (+00:30:00), effective 2016-06-01 02:30:00. The last change before that was back in 2007. The most likely explanation for the observed behavior is wonkiness in the timezone table. Especially in regards to future dates. – spencer7593 Sep 29 '16 at 18:21
  • then probably an obsolete/outdated/wrong TZ table. politicians LOVE to mess with pointless stuff like this, but that leaves a LOT of systems with outdated tz info. – Marc B Sep 29 '16 at 18:35

1 Answers1

0

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140