问题描述
我将UTC日期时间值存储在MySQL中.我使用CONVERT_TZ来处理时区转换，以查询/保存数据库中UTC的本地日期时间.
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.
在测试中，我注意到了转换工作方式的这一奇特之处.谁能解释为什么使用-4:00小时偏移量时MySQL会增加23秒，而不使用等效时区标签时为何会增加23秒?
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
我正在Windows XP笔记本电脑上运行MySQL 5.0.67-community-nt-log.我正在从本地托管的phpMyAdmin 3.1.5运行查询，并且在PHP 5.2.8的我自己的应用程序中也可以看到它.
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.
与我的Dreamhost帐户相比，两个查询都返回正确的日期时间('2009-09-06 21:00:00').他们在Linux和PHP 5.2.6上运行MySQL 5.0.45日志.
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.
为什么我自己安装的MySQL有此差异?
Why would my own install of MySQL have this discrepancy?
推荐答案
大概两个安装的时区表不同.时区数据存储在mysql数据库的一堆表中.
Presumably the timezone tables are different on the 2 installs.The timezone data is stored in a bunch of tables in the mysql database.
在UNIX计算机上，它们通常是从系统时区文件生成的，但是Windows安装可能使用了mysql提供的文件:
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
该数据已启用leap秒.要禁用它们，您可以运行此更新:
That data has leap seconds enabled. To disable them you can run this update:
update mysql.time_zone set Use_leap_seconds ='N';
然后重新启动服务器.
这篇关于为什么MySQL CONVERT_TZ会更改时区调整后的秒数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！