如何在MySql的Select查询中将UTC日期转换为本地时区？

Question

我在使用MySql数据库的一个查询中使用了Where条件。我的问题是，我的表中有一个displaytime列，但该表列显示的数据是UTC时间，我想将该displaytime列转换为本地时区。那么如何在查询中提供此功能呢？

我已经搜索了相关内容，并知道类似于SELECT CONVERT_TZ()的东西可以解决问题，但这对我不起作用。

以下是我的查询，我需要将displaytime转换为本地时区...所以有人可以指导我吗？

WHERE displaytime >= '2012-12-01 00:00:00'
  AND displaytime <='2013-02-22 23:59:59'
  AND ct.organizationId IN (
    SELECT t.organizationId
      FROM organization_ AS t
      JOIN organization_ AS p ON t.treePath LIKE CONCAT(p.treePath, '%')
     WHERE p.organizationId = 10707

样本数据

- User 1531343

---

看起来你的mysql时区表是空的。你需要运行mysql_tzinfo_to_sql命令，以便CONVERT_TZ()能够正确地工作。 - ursuleacv

我们在哪里可以获取mysql_tzinfo_to_sql命令？ - user1073214

如果您有偏移量，可以使用此函数DATE_ADD(displaytime, INTERVAL {+-YOUR_OFFSET_IN_MINUTES} MINUTE)。对于DATE_SUB，无需使用IF/ELSE语句，始终使用DATE_ADD即可，减去或加上取决于偏移值，该值可能为正数或负数。 - vinsa

Answer 1

使用SELECT CONVERT_TZ()可以实现，但对我不起作用。

为什么？你出现了什么错误？

SELECT CONVERT_TZ(displaytime,'GMT','MET');

如果您的列类型是时间戳或日期，则应该可以正常工作。

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

测试这是如何工作的：

SELECT CONVERT_TZ(a_ad_display.displaytime,'+00:00','+04:00');

检查您的时区表

SELECT * FROM mysql.time_zone;
SELECT * FROM mysql.time_zone_name;

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

如果这些表是空的，那么你还没有初始化时区表。根据上面的链接，你可以使用 mysql_tzinfo_to_sql 程序加载时区表。请尝试此操作。

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo

如果无法正常工作，请阅读更多：http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html

- iiro

---

查询结果返回了空值。查询语句为：SELECT CONVERT_TZ(a_ad_display.displaytime,'GMT','MET') as date_ from a_ad_display; 我的列是DATETIME类型。 - BhavikKama

你能发布一下 SHOW CREATE TABLE a_ad_display\G; 的输出结果吗？ - iiro

这没有提供任何帮助。请提供您的表结构和示例数据。 - iiro

在我的表结构中，有一列我想要将本地日期转换为它的类型是[DATETIME]..请查看我的更新问题以获取示例数据。 - BhavikKama

如果那是样本数据，那么你会得到空值，因为你的列值为空。因此，我认为那是样本结果，而不是样本数据？无论如何，我已经编辑了我的答案。 - iiro

Answer 2

在我的情况下，如果服务器上没有可用的时区，这个方法非常有效：

SELECT CONVERT_TZ(`date_field`,'+00:00',@@global.time_zone) FROM `table`

注意：global.time_zone使用的是服务器时区。您必须确保它具有所需的时区！

- Felix Geenen

---

我在UTC时间戳中多了10-15秒。 - Shashank Shah

Answer 3

 select convert_tz(now(),@@session.time_zone,'+05:30')

将'+05:30'替换为所需时区。点击这里查看 - https://dev59.com/EXA85IYBdhLWcg3wHvw0#3984412

要格式化为所需的时间格式，例如：

 select DATE_FORMAT(convert_tz(now(),@@session.time_zone,'+05:30') ,'%b %d %Y %h:%i:%s %p') 

你将得到类似于这个 -> Dec 17 2014 10:39:56 AM

- Som