Why does MySQL CONVERT_TZ alter the seconds after

Question

I'm storing UTC datetime values in MySQL. I use CONVERT_TZ to handle timezone conversion to query/save local datetimes to/from UTC in the database.

Upon testing I noticed this strange peculiarity in how the conversion works. Can anyone explain why it is MySQL is adding 23 seconds when using the -4:00 hour offset, but not when using the equivalent timezone label?

select convert_tz('2009-06-12 01:00:00', 'UTC', '-4:00')
2009-06-11 21:00:23

select convert_tz('2009-06-12 01:00:00', 'UTC', 'US/Eastern')
2009-06-11 21:00:00

I'm running against MySQL 5.0.67-community-nt-log on my Windows XP laptop. I'm running the query from a locally hosted phpMyAdmin 3.1.5 and I can see it also within my own app in PHP 5.2.8.

Comparing with my Dreamhost account, both queries return the proper datetime ('2009-09-06 21:00:00'). They run MySQL 5.0.45-log on Linux and PHP 5.2.6.

Why would my own install of MySQL have this discrepancy?

Answer 1

Presumably the timezone tables are different on the 2 installs. The timezone data is stored in a bunch of tables in the mysql database.

On a unix machine they are usually generated from the system timezone files, but your windows installation probably used the files provided by mysql:

http://dev.mysql.com/downloads/timezones.html

That data has leap seconds enabled. To disable them you can run this update:

update mysql.time_zone set Use_leap_seconds ='N';

and then restart your server.