基于时区偏移量的MySql搜索时间戳

Question

我已经创建了一个与原始样本具有相同场景的示例表。 表名 “记录” 数据库中的 “测试”

数据库时区设置为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年9月14日”

我在下面试图查询

1. 记录日期之间：
2. 记录

 
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:11 
15 motion 2017-09-14 00:07:09 
1 motion 2017-09-13 16:20:41 

对于给定日期：

 
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 DESC 
result: 
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; 

--- EDITED 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;