MySQL CONVERT_TZ() [英] MySQL CONVERT_TZ()

问题描述

我正在尝试建立一个数据库来存储用户指定的每日警报时间.例如，用户希望在每天早上 7:00 到早上 7:30 之间满足某个条件时收到警报.在尝试实现这一点时，我需要适应夏令时.这是我尝试的解决方案:

I am trying to set up a database that stores daily alert times as specified by users. For example, the user wants to receive an alert if some criterion is met each day between 7:00 AM and 7:30 AM. In trying to implement this, I need to accommodate daylight saving time. Here's my attempted solution:

1. 将用户本地时区(长格式，例如美国/东部")信息存储在一个表中(比如 userInfo)，并将闹钟时间存储在另一个表中(比如 userAlarms).
2. 查询userAlarms表时，通过CONVERT_TZ(UTC_TIME(), 'UTC', userInfo.tz)将UTC时间转换为userInfo表中存储的tz列指定的用户本地时间.

1. Store the users local time zone (in long form, e.g. "US/Eastern") information in one table (say userInfo), and the alarm times in another table (say userAlarms).
2. When querying the userAlarms table, convert UTC time into the users local time as specified by the tz column stored in the userInfo table via CONVERT_TZ(UTC_TIME(), 'UTC', userInfo.tz).

问题 1. 根据我的理解，指定时区名称(如美国/东部)应该考虑夏令时.例如，在 1 月 1 日调用 CONVERT_TZ('00:00:00', 'UTC', 'US/EASTERN') 应该产生 '19:00:00'，但在 7 月 1 日调用应该产生'20:00:00'.我说得对吗?

Question 1. From my understanding, specifying the time zone name (like US/Eastern) should take daylight saving time into account. For example, calling CONVERT_TZ('00:00:00', 'UTC', 'US/EASTERN') on January 1 should yield '19:00:00', but on July 1 the call should yield '20:00:00'. Am I correct?

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

Question 2. If Q1 is correct, do I need to constantly update MySQL's time zone table to keep the time zone UTC offsets up to date?

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

Question 3. The sample given in the MySQL documentation SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') yields "NULL" when run on my server. Could this be caused by not having the time zone tables set-up?

我该如何检查?

推荐答案

如果结果为 null，则 TZ 表尚未设置:

If this yields null then the TZ tables have not been set up:

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

如果您没有设置时区表，您可以更新小时在用户表中偏移然后执行:

If you do not have the time zone tables set up you could update the hour offset in the user table and then do:

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

不过，您仍然需要一种方法来更新用户的时区.

You'd still need a way to update the user's time zone however.

如果您是为 Web 应用程序编写此代码，则可以通过 javascript 获取时区，这是一个 文章 描述了如何(尚未尝试过，但看起来它会起作用).

If you are writing this for a web application you can get the time zone via javascript, here's an article that describes how (haven't tried this but it looks like it'll work).

对上面间隔"的一些解释......

A bit of an explanation with respect to 'interval' above...

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

One of the more trick constructs in MySQL is the use of the INTERVAL keyword, best shown by example the (numeric value can be an expression or the field value)

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

你可以随心所欲地添加和减去它们，这就是为什么我从不打扰日期/时间加/减/转换功能

You can add them and subtract them anyway you like, this is why I never bother with the date/time add/subtract/convert functions

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 |
+---------------------+---------------------+

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

You can use negative numbers (should be good for negative time zone offsets) these are the same:

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 |
+---------------------+---------------------+

这篇关于MySQL CONVERT_TZ()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！