MySql search time stamp based on timezone offset

Question

I have created a sample table with same scenario as my original one. Table name "records" in database "test"

database timezone is 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 have to make two queries using time conversion using CONVERT_TZ (mysql conversion function )

I need two queries : 1. fetch records between date "today" and "today - 30 days back" 2. fetch records for given date like "2017-09-14"

I have tried below queries

records between date :

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

records for given date :

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

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; --- Updated first query — Harjeevan Tanda, Jan 27 '18 at 05:16

Jacques Amar · Accepted Answer · 2017-09-25T20:37:26.630

2

For the first, assuming your system is running at 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;

If NOW() is the current time zone

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

For the second, It's really important to know which Timezone you want to compare with.

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

Will put the date to the day before or 2017-09-13 (we have moved the comparison to the other side of the equation, so we have to change the order

changing the >= to = and just use 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;

The simplest comparison will be against UTC and the formula will be

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

edited Sep 25 '17 at 20:37

answered Sep 22 '17 at 23:29

Jacques Amar

1,803
1
10
12

Jacques Amar, Thanks for reply. For the second query I need to fetch records for date "2017-09-14" it is showing 0 result – Harjeevan Tanda Sep 25 '17 at 13:43
Sorry, I didn't read my text and forgot yo put in the date! EDITED – Jacques Amar Sep 25 '17 at 17:44
Jacques Amar again thanks for drilling down I still have same issue 0 records. Have you created same data on your system? – Harjeevan Tanda Sep 25 '17 at 20:00
Jacques Amar, I have updated the question there was error in First query. Is it possible that my MySQL version have issue with CONVERT_TZ – Harjeevan Tanda Mar 08 '18 at 04:47
There are no errors I can think of. Note however that the dates are now stale and checking for 30 days prior will not give out any results. You will need to update the dates in your records to compare to 30 days, as we use NOW() for the date – Jacques Amar Mar 08 '18 at 19:45
Thanks @Jacques updated the query with the static date. – Harjeevan Tanda Dec 07 '19 at 11:38

MySql search time stamp based on timezone offset

1 Answers1