mysql 查询时区转换

Question

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

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

我正在尝试编写一个查询来提取转换为其原始时区的 showtime。我这样做。

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 附加到现有值。（已测试并按预期返回值）