MySQL CONVERT_TZ()

MySQL CONVERT_TZ()

提问by johnnyspo

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:

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

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. 将用户本地时区（长格式，例如“美国/东部”）信息存储在一个表中（比如 userInfo），并将闹钟时间存储在另一个表中（比如 userAlarms）。
2. 查询userAlarms表时，将UTC时间转换为userInfo表中存储的tz列指定的用户本地时间CONVERT_TZ(UTC_TIME(), 'UTC', userInfo.tz)。

Question 1. From my understanding, specifying the time zone name (like US/Eastern) shouldtake 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?

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

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?

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

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?

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

How can I check this?

我该如何检查？

回答by dave

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

如果这产生 null 则 TZ 表尚未设置：

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.

但是，您仍然需要一种方法来更新用户的时区。

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

如果您正在为 Web 应用程序编写此代码，则可以通过 javascript 获取时区，这里有一篇文章描述了如何操作（尚未尝试过，但看起来可以）。

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

关于上面的“间隔”的一些解释......

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

MySQL 中更巧妙的构造之一是使用INTERVAL关键字，最好通过示例展示（数值可以是表达式或字段值）

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

回答by philwinkle

I found this thread helpful and decided to share the doc page for importing this information. I did exactly as instructed below in CentOS and in RHEL and it worked flawlessly. I am now able to use the CONVERT_TZ function with arguments like "GMT" and "US/Eastern".

我发现此线程很有帮助，并决定共享用于导入此信息的文档页面。我在 CentOS 和 RHEL 中完全按照下面的说明进行操作，并且它完美无缺。我现在可以使用带有“GMT”和“US/Eastern”等参数的 CONVERT_TZ 函数。

From the MySQL documentation this is how to import the MySQL time zone table information:

从 MySQL 文档这是如何导入 MySQL 时区表信息：

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

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

---

---

For all users running MySQL 4.1.3 or later on a Unix-based system (recall this doesn't work on Windows systems yet):

对于在基于 Unix 的系统上运行 MySQL 4.1.3 或更高版本的所有用户（回想一下，这还不适用于 Windows 系统）：

Populate the time zone tables.

填充时区表。

To do so, run the mysql_tzinfo_to_sqlprogram, provided with the MySQL distribution. mysql_tzinfo_to_sqlreads the operating system time zone files and generates SQL statements from them. The SQL statements are then processed by mysql, to load the time zone tables.

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

To run mysql_tzinfo_to_sqlsuccessfully, one needs to know where the server machine's operating system time zone files are stored; check for a directory with a name similar to /usr/share/zoneinfo. Pass the directory name on the command line to mysql_tzinfo_to_sql, and send the output into the mysql program. Here's an example.

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

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

Note: The above command assumes that "mysql_tzinfo_to_sql" and "mysql" are in your path. If they're not, you'll need to supply the full path to both when running the command, or switch into the mysql bin folder and use a command like so:

注意：上面的命令假设“mysql_tzinfo_to_sql”和“mysql”在你的路径中。如果不是，则需要在运行命令时提供两者的完整路径，或者切换到 mysql bin 文件夹并使用如下命令：

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

回答by David Parks

Q1: Yes, the CONVERT_TZ takes daylight savings time into account for you. This information and the time that DST starts/ends for each time zone is stored in the time_zone_* tables.

Q1：是的，CONVERT_TZ 会为您考虑夏令时。此信息以及每个时区的 DST 开始/结束时间存储在 time_zone_* 表中。

Q2: Yes, as stated in the mysql docs, the time zone information changes per the politics of each area. You'll have to update the time_zone_* tables every time a change occurs. Sucks to be IT sometimes, this is one of them.

Q2：是的，正如 mysql 文档中所述，时区信息根据每个地区的而变化。每次发生更改时，您都必须更新 time_zone_* 表。有时做 IT 很糟糕，这就是其中之一。

Q3: These are the 5 timezone tables, query them to see if they have anything in them:

Q3：这是 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;