MySQL - запрашивает записи, созданные "сегодня", но дата в UTC

В настоящее время я храню свои даты в UTC, и пока что все работает нормально. Но теперь я хочу запросить записи, созданные в определенный день, как сегодня, в моем собственном часовом поясе, который является восточным.

Из-за разницы в часах записи, которые я сейчас создаю, будут иметь завтрашнюю дату. Например, часть даты в настоящее время будет 2016-06-20 в моем часовом поясе, но UTC 2016-06-21,

Как я могу точно получить записи за определенный день в своем часовом поясе, когда записи хранятся в формате UTC? Я думал, что я мог бы использовать convert_tz функция для преобразования сохраненного значения в восточное и использовать его как часть моего where пункт. Таким образом, даты совпадают. Это надежное решение?

Источник

4 ответа

Решение

Преобразуйте границы даты и времени, которые вы ищете, в UTC.

Функция CONVERT_TZ удобна. Что-то вроде этого:

WHERE my_utc_date_time_col >= CONVERT('2016-02-20','EST5EDT','+00:00') 
  AND my_utc_date_time_col <  CONVERT('2016-02-20','EST5EDT','+00:00') + INTERVAL 1 DAY

(Это предполагает, что вы заполнили таблицы часовых поясов MySQL, поэтому поддерживаются так называемые часовые пояса.)

Это эффективно эквивалентно:

WHERE my_utc_date_time_col >= '2016-02-20' + INTERVAL 4 HOUR
  AND my_utc_date_time_col <  '2016-02-20' + INTERVAL 4 HOUR + INTERVAL 1 DAY

или же

WHERE my_utc_date_time_col >= '2016-02-20 04:00:00'
  AND my_utc_date_time_col <  '2016-02-21 04:00:00'

Если вы хотите сослаться на функцию, такую ​​как NOW() чтобы получить текущую дату, тогда будет важен часовой пояс соединения MySQL, потому что возвращаемое значение datetime будет в time_zone соединения MySQL.

SHOW VARIABLES LIKE 'time_zone' ;

Обратите внимание, что мы предпочитаем выполнять преобразование на литеральной стороне, а не на столбце, потому что если мы сделаем это на столбце, это заставит MySQL выполнить преобразование для каждой строки в таблице и сравнить литерал. Когда мы выполняем преобразование на буквальной стороне, это позволяет MySQL эффективно использовать операцию сканирования диапазона индекса (с соответствующим доступным индексом).

Источник

Функция CONVERT_TZ - это то, что вам нужно. Но я не согласен с:

Я мог бы использовать функцию convert_tz, чтобы преобразовать сохраненное значение в восточное, и использовать его как часть моего предложения where.

Потому что с помощью функций на WHERE пункт может снизить производительность.

Если вы используете хранимые процедуры, я предлагаю сначала преобразовать введенные даты, а затем использовать их в SELECT заявление. Это будет выглядеть примерно так:

SET @fromDate = CONVERT_TZ(@fromDate, ...) --Convert to UTC
SET @toDate = CONVERT_TZ(@toDate, ...) --Convert to UTC

SELECT *
    FROM TableA A
    WHERE A.FromDate BETWEEN @fromDate AND @toDate
Источник

http://ee1.php.net/manual/en/class.datetime.php

$foo = new DateTime("today", new DateTimeZone("America/Vancouver"));
var_dump($foo->format('c'), $foo->format('U'));
$foo->setTimeZone(new DateTimeZone("UTC"));
var_dump($foo->format('c'), $foo->format('U'), $foo->format('Y-m-d H:i:s'));

Выход:

string(25) "2016-06-20T00:00:00-07:00"
string(10) "1466406000"
string(25) "2016-06-20T07:00:00+00:00"
string(10) "1466406000"
string(19) "2016-06-20 07:00:00"
Источник

https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

          SELECT DATE(CONVERT_TZ(NOW(), 'UTC', 'America/Vancouver')) as VancouverToday;

Я не говорю, что это оптимизировано, быстро и т. Д. Я просто говорю, что если вы сохранили значения UTC и / или ваш сервер находится в формате UTC, и вы ищете сравнение для Today, это показывает, что вы настроили «сегодня». для вашего часового пояса.

Источник
Другие вопросы по тегам