如何为特定地点的时区(时间戳均为UTC,并且地点已命名时区,例如美国/温哥华)选择从昨天午夜到午夜的所有记录?
请注意,我不是在问如何将日期和时间转换为另一个时区。我要问的是如何[最好]本地化日期范围比较。
假设MySQL数据库会话的时区为UTC(即time_zone='+00:0')。。。
假设您想对所有行使用相同的时区(即,根据行的内容不使用不同的时区…
取用户指定时区中"午夜"的值,并将其转换为UTC。例如,2016-04-16 00:00 CST6CDT(即美国/芝加哥)转换为2016-04-16 05:00 UTC。
假设您的表列名为utc_timestamp_col,数据类型为timestamp,那么您的查询将如下所示:
SELECT ...
FROM mytable t
WHERE t.utc_timestamp_col >= '2016-04-16 05:00' + INTERVAL -1 DAY
AND t.utc_timestamp_col < '2016-04-16 05:00' + INTERVAL 0 DAY
如果您已经填充了MySQL时区表,则可以使用MySQL对命名时区的支持。http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html
您可以构建一个表达式,使您在命名时区中获得上一个"午夜"。
以下是一个演示查询:
- 验证会话时区是否为UTC
- 返回UTC中的当前日期和时间
- 转换为本地时区CST6CDT
- 截断到午夜
- 转换回UTC
例如
SELECT @@session.time_zone AS `time_zone`
, NOW() AS `now_utc`
, CONVERT_TZ(NOW(),'UTC','CST6CDT') AS `now_CST6CDT`
, DATE(CONVERT_TZ(NOW(),'UTC','CST6CDT')) AS `midnight_CST6CDT`
, CONVERT_TZ(DATE(CONVERT_TZ(NOW(),'UTC','CST6CDT')),'CST6CDT','UTC')
AS midnight_CST6CDT_utc
退货:
time_zone now_utc now_CST6CDT midnight_CST6CDT midnight_CST6CDT_utc
--------- ------------------- ------------------- ---------------- --------------------
UTC 2016-04-17 01:53:31 2016-04-16 20:53:31 2016-04-16 2016-04-16 05:00:00
使用名为"美国/芝加哥"的时区演示同样的东西
SELECT @@session.time_zone AS `time_zone`
, NOW() AS now_utc
, CONVERT_TZ(NOW(),'UTC','America/Chicago') AS `now_America/Chicago`
, DATE(CONVERT_TZ(NOW(),'UTC','America/Chicago')) AS `midnight_America/Chicago`
, CONVERT_TZ(DATE(CONVERT_TZ(NOW(),'UTC','America/Chicago')),'America/Chicago','UTC')
AS `midnight_America/Chicago_utc`
返回相同的结果:
time_zone now_utc now_America/Chicago midnight_America/Chicago midnight_America/Chicago_utc
--------- ------------------- ------------------- ------------------------ ----------------------------
UTC 2016-04-17 01:57:19 2016-04-16 20:57:19 2016-04-16 2016-04-16 05:00:00
跟进
如果我需要在查询中进行这种时区转换,我会使用内联视图来返回这个相当复杂的表达式的值,以使外部语句更简单。例如,别名为"d"的内联视图以名为"dt"的列的形式返回值,该列可以在外部查询中引用。
由于该内联视图只返回一行,因此我们可以使用JOIN
来mytable,而无需复制任何行。我们可以将谓词从WHERE子句移动到ON子句。例如
SELECT ...
FROM ( SELECT CONVERT_TZ(DATE(CONVERT_TZ(NOW(),'UTC','CST6CDT')),'CST6CDT','UTC') AS dt
) d
JOIN mytable t
ON t.utc_timestamp_col >= d.dt + INTERVAL -1 DAY
AND t.utc_timestamp_col < d.dt + INTERVAL 0 DAY