MySQL CONVERT_TZ()

我正在尝试设置一个数据库,用于存储用户指定的每日警报时间.例如,如果在每天上午7:00到7:30之间满足某些标准,则用户想要接收警报.在尝试实现这一点时,我需要适应夏令时.这是我尝试的解决方案:

将用户本地时区(长格式,例如"US/Eastern")信息存储在一个表(例如userInfo)中,将警报时间存储在另一个表(例如userAlarms)中.

查询userAlarms表时,将UTC时间转换为userInfo表中存储的tz列指定的用户本地时间CONVERT_TZ(UTC_TIME(), 'UTC', userInfo.tz).

问题1.根据我的理解,指定时区名称(如美国/东部)应考虑夏令时.例如,CONVERT_TZ('00:00:00', 'UTC', 'US/EASTERN')1月1 日的呼叫应该会产生'19:00:00',但是在7月1日,呼叫应该产生'20:00:00'.我对么？

问题2.如果Q1正确,我是否需要不断更新MySQL的时区表以保持时区UTC偏移最新？

问题3. MySQL文档中给出的示例在我的服务器上运行时SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')产生" NULL".这可能是由于没有设置时区表造成的吗？

我怎么检查这个？

如果这产生null,则表示尚未设置TZ表:

SELECT CONVERT_TZ(now(),'US/Eastern','US/Central');

如果您没有设置时区表,则可以更新用户表中的小时偏移量,然后执行以下操作:

select utc_timezone() - interval user_timezone_offset_in_hours hour
from userinfo a
where user_id = 999;

但是,您仍然需要一种更新用户时区的方法.

如果您正在为Web应用程序编写此文件,您可以通过javascript获取时区,这里有一篇文章介绍了如何(没有尝试过这个但看起来它会起作用).

关于"间隔"的一些解释......

MySQL中一个比较简单的构造是使用INTERVAL 关键字,最好通过示例显示(数值可以是表达式或字段值)

select now() today, now() - interval 1 day yesterday;
+---------------------+---------------------+
| today               | yesterday           |
+---------------------+---------------------+
| 2011-05-26 13:20:55 | 2011-05-25 13:20:55 |
+---------------------+---------------------+

您可以添加它们并随意减去它们,这就是为什么我从不 打扰日期/时间加/减/转换函数

select now() a, now() - interval 1 day + interval 4 hour + interval 8 minute b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-05-26 13:24:16 | 2011-05-25 17:32:16 |
+---------------------+---------------------+

您可以使用负数(应该适用于负时区偏移)这些是相同的:

select now() - interval 1 month a, now() + interval -1 month b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-04-26 13:38:05 | 2011-04-26 13:38:05 |
+---------------------+---------------------+

我发现这个帖子很有帮助,并决定共享文档页面以导入此信息.我完全按照下面在CentOS和RHEL中的指示行事,它完美无缺.我现在能够使用带有"GMT"和"US/Eastern"等参数的CONVERT_TZ函数.

从MySQL文档中,这是如何导入MySQL时区表信息:

http://dev.mysql.com/tech-resources/articles/4.1/time.html

对于在基于Unix的系统上运行MySQL 4.1.3或更高版本的所有用户(请记住,这在Windows系统上不起作用):

填充时区表.

为此,请运行mysql_tzinfo_to_sqlMySQL发行版提供的程序.mysql_tzinfo_to_sql读取操作系统时区文件并从中生成SQL语句.然后由mysql处理SQL语句,以加载时区表.

要mysql_tzinfo_to_sql成功运行,需要知道服务器计算机的操作系统时区文件的存储位置; 检查名称类似的目录/usr/share/zoneinfo.将命令行上的目录名传递给mysql_tzinfo_to_sql,并将输出发送到mysql程序.这是一个例子.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

注意:上面的命令假定您的路径中有"mysql_tzinfo_to_sql"和"mysql".如果它们不是,那么在运行命令时你需要提供两者的完整路径,或者切换到mysql bin文件夹并使用如下命令:

shell> ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -u root mysql

Q1:是的,CONVERT_TZ会将夏令时考虑在内.此信息以及每个时区的DST开始/结束时间存储在time_zone_*表中.

Q2:是的,正如mysql文档中所述,时区信息根据每个区域的政治而变化.每次发生更改时,您都必须更新time_zone_*表.有时候很难成为IT,这就是其中之一.

问题3:这些是5个时区表,查询它们以查看它们中是否有任何内容:

select * from mysql.time_zone_transition_type;
select * from mysql.time_zone_transition;
select * from mysql.time_zone_name;
select * from mysql.time_zone_leap_second;
select * from mysql.time_zone;