php - mysql查询时区转换

Question

我已将显示时间存储在所有东部地区和时区设置表中的 ID 中。时区设置表有 Timezone_id、GMT 偏移量和时区名称。

GMT 偏移量存储为 +6、-4、+3.5、-4.5 等。

我正在尝试编写一个查询以将放映时间转换为其原始时区。我就是这样做的。

SELECT Date_format( CONVERT_TZ( CONVERT_TZ(A.START_TIME,  '+00:00',  '-5:00'),  
'+00:00',     CONCAT(B.GMT_OFFSET , ':00' )), '%Y-%m-%e %r:%i')  
AS  'start_time' from shows A, tz_settings B AND <<JOINS>>;

我在这里所做的是首先将时间从 ET 转换为 GMT，然后应用 GMT 偏移量。

START_TIME 的日期格式类似于“2012-4-23 10:15:00”此外，还有一个问题是将“3.5”等格式转换为“3.30”以传递给 Convert_TZ

编辑:表结构。主要领域。

   shows: 'id', 'show_name', 'stat_time', 'tz_id'
   tz_settings: 'tz_id', 'gmt_offset','tz_name'

有什么想法吗？任何其他功能都有助于混合？

Answer 1

我真的不知道你遇到了什么问题，但我只是用你的结构构建了 2 个 MYSQL 表:

MySQL 表

TABLE shows ['id', 'show_name', 'start_time', 'tz_id']
TABLE tz_settings ['tz_id', 'gmt_offset','tz_name']

使用这个 MySQL 查询:

SELECT Date_format( CONVERT_TZ( CONVERT_TZ( A.START_TIME, '+00:00', '-5:00' ) , '+00:00', CONCAT( B.GMT_OFFSET, ':00' ) ) , '%Y-%m-%e %r:%i' ) AS 'start_time'
FROM shows A, tz_settings B
WHERE A.id =1
AND B.tz_id = A.tz_id

结果如预期!

---

无论如何，您应该以完整格式存储 GMT OFFSET 以简化您的查询，从而丢失 CONCAT 并提高性能。例如:存储 +05:00 而不是 +5

这对您有任何帮助吗？

---

编辑为包含 MYSQL IF 和替换

SELECT Date_format( CONVERT_TZ( CONVERT_TZ( A.STAT_TIME, '+00:00', '-5:00' ) , '+00:00', if( B.GMT_OFFSET LIKE '%.5', REPLACE( B.GMT_OFFSET, '.5', ':30' ) , CONCAT( B.GMT_OFFSET, ':00' ) ) ) , '%Y-%m-%e %r:%i' ) AS 'start_time'
FROM shows A, tz_settings B
WHERE A.`id` =1
AND B.`tz_id` = A.`tz_id`

因此，如果存储的 GMT OFFSET 为 .5，它将被替换为 :30，否则，它会将 :00 附加到现有值。 (测试并按预期返回值)