MySql 根据时区偏移查找时间戳

Question

我创建了一个示例表，其场景与我的原始表相同。数据库“test”中的表名“records”

数据库时区设置为UTC(SET time_zone = "+00:00";)

`records` (`id`, `name`, `time_created`)(1,  'motion', '2017-09-13 16:20:41'),(2,  'motion', '2017-09-13 16:20:57'),(3,  'motion', '2017-09-13 16:21:24'),(4,  'motion', '2017-09-13 16:21:40'),(5,  'motion', '2017-09-13 16:26:38'),(6,  'motion', '2017-09-13 17:09:00'),(7,  'motion', '2017-09-13 17:09:16'),(8,  'motion', '2017-09-13 22:14:37'),(9,  'motion', '2017-09-13 22:23:53'),(10, 'motion', '2017-09-13 22:24:08'),(11, 'motion', '2017-09-13 22:24:24'),(12, 'motion', '2017-09-13 23:45:17'),(13, 'motion', '2017-09-13 23:45:36'),(14, 'motion', '2017-09-13 23:45:54'),(15, 'motion', '2017-09-14 00:07:09'),(16, 'motion', '2017-09-14 00:07:24'),(17, 'motion', '2017-09-14 00:07:42'),(18, 'motion', '2017-09-19 09:42:11'),(19, 'motion', '2017-09-19 09:42:27'),(20, 'motion', '2017-09-19 09:42:44'),(21, 'motion', '2017-09-19 11:21:08'),(22, 'motion', '2017-09-19 11:21:23'),(23, 'motion', '2017-09-19 11:21:38'),(24, 'motion', '2017-09-19 11:21:54'),(25, 'motion', '2017-09-19 11:48:13'),(26, 'motion', '2017-09-13 16:20:41'),(27, 'motion', '2017-09-13 16:20:57'),(28, 'motion', '2017-09-13 16:21:24'),(29, 'motion', '2017-09-13 16:21:40'),(30, 'motion', '2017-09-13 16:26:38'),(31, 'motion', '2017-09-13 17:09:00'),(32, 'motion', '2017-09-13 17:09:16'),(33, 'motion', '2017-09-13 22:14:37'),(34, 'motion', '2017-09-13 22:23:53'),(35, 'motion', '2017-09-13 22:24:08'),(36, 'motion', '2017-09-13 22:24:24'),(37, 'motion', '2017-09-13 23:45:17'),(38, 'motion', '2017-09-13 23:45:36'),(39, 'motion', '2017-09-13 23:45:54'),(40, 'motion', '2017-09-14 00:07:09'),(41, 'motion', '2017-09-14 00:07:24'),(42, 'motion', '2017-09-14 00:07:42'),(43, 'motion', '2017-09-19 09:42:11'),(44, 'motion', '2017-09-19 09:42:27'),(45, 'motion', '2017-09-19 09:42:44'),(46, 'motion', '2017-09-19 11:21:08'),(47, 'motion', '2017-09-19 11:21:23'),(48, 'motion', '2017-09-19 11:21:38'),(49, 'motion', '2017-09-19 11:21:54'),(50, 'motion', '2017-09-19 11:48:13');

我必须使用 CONVERT_TZ ( mysql conversion function ) 使用时间转换进行两个查询

我需要两个查询: 1. 获取日期“今天”和“今天 - 30 天前”之间的记录 2. 获取给定日期的记录，如“2017-09-14”

我试过下面的查询

1. 日期之间的记录:

SELECT * FROM test.records WHERE name LIKE '%motion%' AND CONVERT_TZ(time_created ,'+00:00','-7:0') BETWEEN DATE_SUB(CONVERT_TZ('2017-09-20 11:48:13' ,'+00:00','-7:0'), INTERVAL 30 DAY) AND CONVERT_TZ('2017-09-20 11:48:13','+00:00','-7:0') GROUP BY DATE(time_created) ORDER BY ID DESC;result : 18  motion  2017-09-19 09:42:1115  motion  2017-09-14 00:07:091   motion  2017-09-13 16:20:41

1. 给定日期的记录:

SELECT name,id, CONVERT_TZ(time_created ,'+00:00','-7:0') as time_created,DATE_FORMAT( CONVERT_TZ(time_created ,'+00:00','-7:0') , '%h:%i:%s %p') as new_format_time FROM test.records WHERE name LIKE '%motion%' AND DATE( CONVERT_TZ(time_created ,'+00:00','-7:0') ) = '2017-09-14' ORDER BY ID DESCresult:0 records

Answer 1

首先，假设您的系统在 UTC 运行

   SELECT * FROM test.records
   WHERE name LIKE '%motion%' AND
      time_created >= DATE_SUB(CONVERT_TZ(NOW(),'+00:00','-7:0') , INTERVAL 30 DAY)
   GROUP BY DATE(time_created) ORDER BY ID DESC;

如果NOW()是当前时区

   SELECT * FROM test.records
   WHERE name LIKE '%motion%' AND
      CONVERT_TZ(time_created,'+00:00','-7:0') >= DATE_SUB(NOW(), INTERVAL 30 DAY)
   GROUP BY DATE(time_created) ORDER BY ID DESC;

--- 编辑 201/09/25 ---

其次，了解要与哪个时区进行比较非常重要。

select DATE(CONVERT_TZ('2017-09-14 00:07:25','+00:00', '-07:00'));

将日期放在前一天或2017-09-13(我们已经将比较移到了等式的另一边，所以我们必须改变顺序

将 >= 更改为 = 并仅使用 DATE()

   SELECT *
   FROM test.records
   WHERE name LIKE '%motion%' AND
     DATE(CONVERT_TZ(time_created,'-7:0', '+00:00')) = '2017-09-14'
   GROUP BY DATE(time_created) ORDER BY ID DESC;

最简单的比较是与 UTC 进行比较，公式为

   SELECT *
   FROM test.records
   WHERE name LIKE '%motion%' AND
     DATE(time_created) = '2017-09-14'
   GROUP BY DATE(time_created) ORDER BY ID DESC;