首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL;使用位置的时区选择昨天(午夜至午夜)的所有记录

问MySQL;使用位置的时区选择昨天(午夜至午夜)的所有记录
EN

Stack Overflow用户
提问于 2016-04-17 00:23:09
回答 1查看 2.4K关注 0票数 1

我如何选择昨天的所有记录,从午夜到午夜,为特定地点的时区(时间戳都在世界协调时和地点命名时区-例如美国/温哥华)?

请注意,我不是在问如何将一个日期和时间转换为另一个时区。我想问的是,如何最好地本地化日期范围比较。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 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,并且是数据类型时间戳,则您的查询看起来如下所示:

代码语言:javascript
复制
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

您可以在指定的时区中构建一个表达式,该表达式将使您在前面的“午夜”。

下面是一个演示查询:

  • 验证会话time_zone是否为UTC
  • 返回UTC的当前日期和时间
  • 转换为本地时区CST6CDT
  • 截断到午夜
  • 转换回世界协调时

例如:

代码语言:javascript
复制
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

返回:

代码语言:javascript
复制
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

演示同样的东西,使用名为“美国/芝加哥”的时区

代码语言:javascript
复制
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`

返回相同的结果:

代码语言:javascript
复制
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子句。例如:

代码语言:javascript
复制
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
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/36671336

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档