我如何选择昨天的所有记录,从午夜到午夜,为特定地点的时区(时间戳都在世界协调时和地点命名时区-例如美国/温哥华)?
请注意,我不是在问如何将一个日期和时间转换为另一个时区。我想问的是,如何最好地本地化日期范围比较。
发布于 2016-04-17 02:02:07
假设您的MySQL数据库会话的时区是UTC (即timezone =‘+00:00’).
并假设您希望对所有行使用相同的时区(即,根据行的内容而不是不同的时区).
取用户指定时区中“午夜”的值,并将其转换为UTC。例如2016-04-16 00:00 CST6CDT (即美国/芝加哥)改为2016-04-16 05:00协调世界时。
假设您的表列名为utc_timestamp_col,并且是数据类型时间戳,则您的查询看起来如下所示:
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
您可以在指定的时区中构建一个表达式,该表达式将使您在前面的“午夜”。
下面是一个演示查询:
例如:
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 DAYhttps://stackoverflow.com/questions/36671336
复制相似问题