MySql search timestamp based on timezone offset

Question

I created a sample table with the same script as my original. The name of the table "records" in the database "test"

Database time zone

set to 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');

I need to make two queries using time conversion using CONVERT_TZ ( mysql conversion function )

:  1. "" " - 30 ",  2. , "2017-09-14"

• :

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

2. :

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;