Метка времени поиска MySql на основе смещения часового пояса

Я создал образец таблицы с тем же сценарием, что и мой исходный. Название таблицы "records" в базе данных "test"

часовой пояс базы данных установлен в UTC (SET time_zone = "+00: 00";)

`records` (` id`, `name`,` time_created`)

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

Я должен сделать два запроса, используя преобразование времени, используя CONVERT_TZ ( функция преобразования MySQL)

Мне нужно два запроса: 1. получить записи между датой "сегодня" и "сегодня - 30 дней назад" 2. получить записи для данной даты, например "2017-09-14"

Я пробовал ниже запросы

  1. записи между датами: 
SELECT * FROM test.records ГДЕ НАМ НРАВИТСЯ "%motion%" 
AND CONVERT_TZ(time_created,'+00:00','-7:0') МЕЖДУ DATE_SUB(CONVERT_TZ('2017-09-20 11:48:13','+00:00','-7:0'), ИНТЕРВАЛ 30 ДНЯ) И CONVERT_TZ('2017-09-20 11:48:13','+00:00','-7:0') 
GROUP BY DATE(время создания) ORDER BY ID DESC;

результат: 
18 движение 2017-09-19 09:42:11
15 движение 2017-09-14 00:07:09
1 движение 2017-09-13 16:20:41
  1. записи на данную дату: 
ВЫБЕРИТЕ имя, идентификатор, CONVERT_TZ(time_created,'+00:00','-7:0') как time_created,
DATE_FORMAT( CONVERT_TZ(time_created,'+00:00','-7:0'), '%h:%i:%s %p') как new_format_time 
ОТ test.records ГДЕ НАМ НРАВИТСЯ "%motion%" 
AND DATE( CONVERT_TZ(time_created,'+00:00','-7:0')) = '2017-09-14' ЗАКАЗАТЬ ПО ID DESC
результат:
0 записей
Источник

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;
Источник
Другие вопросы по тегам