Поля даты и времени MySQL и летнее время - как я могу сослаться на "дополнительный" час?
Я использую часовой пояс Америки / Нью-Йорка. Осенью мы "отступаем" на час - фактически "набираем" один час в 2 часа ночи. В точке перехода происходит следующее:
сейчас 01:59:00 -04:00
затем через 1 минуту становится:
01:00:00 -05: 00
Так что, если вы просто скажете "1:30 утра", это будет неоднозначно относительно того, имеете ли вы в виду первое время, когда 1:30 катится, или второе. Я пытаюсь сохранить данные планирования в базе данных MySQL и не могу определить, как правильно сохранить время.
Вот проблема:
"2009-11-01 00:30:00" хранится как 2009-11-01 00:30:00 -04: 00
"2009-11-01 01:30:00" хранится как 2009-11-01 01:30:00 -05: 00
Это хорошо и вполне ожидаемо. Но как мне сохранить что-нибудь до 01:30:00 -04: 00? Документация не показывает никакой поддержки для определения смещения, и, соответственно, когда я пытался указать смещение, оно должным образом игнорировалось.
Единственные решения, о которых я подумал, включают в себя настройку сервера на часовой пояс, в котором не используется летнее время, и необходимые преобразования в моих сценариях (для этого я использую PHP). Но это не кажется необходимым.
Большое спасибо за любые предложения.
8 ответов
Честно говоря, типы дат в MySQL не работают и не могут правильно храниться все время, если в вашей системе не установлен часовой пояс с постоянным смещением, например, UTC или GMT-5. (Я использую MySQL 5.0.45)
Это потому, что вы не можете хранить какое-либо время в течение часа до окончания летнего времени. Независимо от того, как вы вводите даты, каждая функция даты будет обрабатывать это время так, как если бы оно было в течение часа после переключения.
Часовой пояс моей системы America/New_York, Попробуем сохранить 1257051600 (вс, 01 ноября 2009 г., 06:00:00 +0100).
Здесь используется собственный синтаксис INTERVAL:
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200
Четное FROM_UNIXTIME() не вернет точное время.
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200
Как ни странно, DATETIME все равно будет хранить и возвращать (только в строковой форме!) Времена в течение "потерянного" часа, когда начинается DST (например, 2009-03-08 02:59:59). Но использование этих дат в любой функции MySQL рискованно:
SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600
Вывод: если вам нужно хранить и получать каждый раз в году, у вас есть несколько нежелательных вариантов:
- Установите системный часовой пояс на GMT + некоторое постоянное смещение. Например, UTC
Храните даты как INT (как обнаружил Аарон, TIMESTAMP даже не надежен)
Представьте, что тип DATETIME имеет некоторый постоянный часовой пояс смещения. Например, если вы находитесь в
America/New_Yorkконвертируйте свою дату в GMT-5 за пределами MySQL, затем сохраняйте как DATETIME (это оказывается необходимым: см. ответ Аарона). Тогда вы должны быть очень осторожны, используя функции даты / времени MySQL, потому что некоторые предполагают, что ваши значения относятся к системному часовому поясу, другие (особенно арифметические функции времени) являются "независимыми от часового пояса" (они могут вести себя так, как если бы время было UTC).
Аарон и я подозреваю, что автоматически генерирующиеся столбцы TIMESTAMP также не работают. И то и другое 2009-11-01 01:30 -0400 а также 2009-11-01 01:30 -0500 будет храниться как неоднозначный 2009-11-01 01:30,
Я понял это для моих целей. Я суммирую то, что я узнал (извините, эти заметки многословны; они так же важны для моего будущего направления, как и все остальное).
Вопреки тому, что я сказал в одном из моих предыдущих комментариев, поля DATETIME и TIMESTAMP ведут себя по-разному. Поля TIMESTAMP (как указывают документы) берут все, что вы отправляете, в формате "ГГГГ-ММ-ДД чч: мм: сс" и преобразует его из текущего часового пояса в время UTC. Обратное происходит прозрачно всякий раз, когда вы получаете данные. Поля DATETIME не делают это преобразование. Они берут все, что вы им отправляете, и просто храните это напрямую.
Ни типы полей DATETIME, ни TIMESTAMP не могут точно хранить данные в часовом поясе, который соблюдает DST. Если вы храните "2009-11-01 01:30:00", в полях нет никакой возможности определить, какую версию 1:30 вы хотели - версию -04:00 или -05:00.
Итак, мы должны хранить наши данные в часовом поясе не в летнее время (например, UTC). Поля TIMESTAMP не могут обрабатывать эти данные точно по причинам, которые я объясню: если ваша система настроена на часовой пояс DST, то то, что вы поместили в TIMESTAMP, может быть не тем, что вы получите обратно. Даже если вы отправите данные, которые вы уже преобразовали в UTC, они все равно будут предполагать, что данные находятся в вашем местном часовом поясе, и выполнят еще одно преобразование в UTC. Эта вынужденная временная поездка туда и обратно, обеспечиваемая TIMESTAMP, вызывает потери, когда ваш местный часовой пояс соблюдает летнее время (так как "2009-11-01 01:30:00" отображается в 2 различных возможных времени).
С DATETIME вы можете хранить свои данные в любом часовом поясе и быть уверенным, что получите все, что отправите (вы не будете вынуждены переходить с потерями туда и обратно, на что навязывают вам поля TIMESTAMP). Таким образом, решение состоит в том, чтобы использовать поле DATETIME и перед сохранением в поле преобразовать из часового пояса вашей системы в любой не-DST-пояс, в котором вы хотите его сохранить (я думаю, что UTC, вероятно, лучший вариант). Это позволяет вам встроить логику преобразования в свой язык сценариев, чтобы можно было явно сохранить UTC-эквивалент "2009-11-01 01:30:00 -04:00" или ""2009-11-01 01:30:00 -05:00".
Еще одна важная вещь, которую стоит отметить, - то, что математические функции даты / времени MySQL не работают должным образом вокруг границ DST, если вы храните свои даты в DST TZ. Так что все больше причин экономить на UTC.
В двух словах, я сейчас делаю это:
При получении данных из базы данных:
Явно интерпретировать данные из базы данных как UTC за пределами MySQL, чтобы получить точную метку времени Unix. Для этого я использую функцию PHP strtotime() или ее класс DateTime. Это не может быть надежно выполнено внутри MySQL с использованием функций MySQL CONVERT_TZ() или UNIX_TIMESTAMP(), поскольку CONVERT_TZ будет выводить только значение "YYYY-MM-DD hh:mm:ss", которое страдает от проблем неоднозначности, а UNIX_TIMESTAMP() предполагает его вход находится в системном часовом поясе, а не в часовом поясе, в котором данные были фактически сохранены (UTC).
При хранении данных в базе данных:
Преобразуйте вашу дату в точное время UTC, которое вы хотите за пределами MySQL. Например: с помощью класса PHP DateTime вы можете указать "2009-11-01 1:30:00 EST" отдельно от "2009-11-01 1:30:00 EDT", затем преобразовать его в UTC и сохранить правильное время UTC в ваше поле DATETIME.
Уф. Большое спасибо за каждый вклад и помощь. Надеюсь, это спасет кого-то еще от головной боли в будущем.
Кстати, я вижу это на MySQL 5.0.22 и 5.0.27
Я думаю, что ссылка micahwittman имеет лучшее практическое решение этих ограничений MySQL: установите часовой пояс сеанса в UTC при подключении:
SET SESSION time_zone = '+0:00'
Тогда вы просто отправляете метки времени Unix, и все должно быть в порядке.
Но как мне сохранить что-нибудь до 01:30:00 -04:00?
Вы можете конвертировать в UTC как:
SELECT CONVERT_TZ('2009-11-29 01:30:00','-04:00','+00:00');
Более того, сохраните даты как поле TIMESTAMP. Это всегда хранится в UTC, а UTC не знает о летнем / зимнем времени.
Вы можете конвертировать из UTC в местное время, используя CONVERT_TZ:
SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM');
Где "+00:00" - это UTC, часовой пояс "from", а "SYSTEM" - местный часовой пояс ОС, в которой работает MySQL.
Mysql по своей природе решает эту проблему, используя таблицу time_zone_name из mysql db. Используйте CONVERT_TZ во время CRUD для обновления даты и времени, не беспокоясь о переходе на летнее время.
SELECT
CONVERT_TZ('2019-04-01 00:00:00','Europe/London','UTC') AS time1,
CONVERT_TZ('2019-03-01 00:00:00','Europe/London','UTC') AS time2;
Эта тема заставила меня взбеситься, так как мы используем TIMESTAMP столбцы с On UPDATE CURRENT_TIMESTAMP (То есть: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) отслеживать измененные записи и ETL в хранилище данных.
В случае, если кто-то задается вопросом, в этом случае, TIMESTAMP вести себя правильно, и вы можете различить две одинаковые даты, преобразовав TIMESTAMP Unix метка времени:
select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;
id recordTimestamp UNIX_TIMESTAMP(recordTimestamp)
1 2012-11-04 01:00:10.0 1352005210
2 2012-11-04 01:00:10.0 1352008810
Я работал над регистрацией количества посещений страниц и отображением их на графике (используя плагин Flot jQuery). Я заполнил таблицу тестовыми данными, и все выглядело хорошо, но я заметил, что в конце графика точки были на один день согласно меткам на оси x. После проверки я заметил, что счетчик просмотров за день 2015-10-25 дважды извлекался из базы данных и передавался во Flot, поэтому каждый день после этой даты перемещался на один день вправо.
После того, как я некоторое время искал ошибку в своем коде, я понял, что именно тогда наступает время перехода на летнее время. Тогда я пришел на эту страницу SO...
... но предложенные решения были излишними для того, что мне было нужно, или у них были другие недостатки. Меня не очень беспокоит то, что я не могу отличить неоднозначные временные метки. Мне просто нужно считать и отображать записи за сутки.
Сначала я получаю диапазон дат:
SELECT
DATE(MIN(created_timestamp)) AS min_date,
DATE(MAX(created_timestamp)) AS max_date
FROM page_display_log
WHERE item_id = :item_id
Затем в цикле, начиная с min_date заканчивая max_date с шагом одного дня (60*60*24), Я получаю счет:
for( $day = $min_date_timestamp; $day <= $max_date_timestamp; $day += 60 * 60 * 24 ) {
$query = "
SELECT COUNT(*) AS count_per_day
FROM page_display_log
WHERE
item_id = :item_id AND
(
created_timestamp BETWEEN
'" . date( "Y-m-d 00:00:00", $day ) . "' AND
'" . date( "Y-m-d 23:59:59", $day ) . "'
)
";
//execute query and do stuff with the result
}
Мое окончательное и быстрое решение моей проблемы было следующим:
$min_date_timestamp += 60 * 60 * 2; // To avoid DST problems
for( $day = $min_date_timestamp; $day <= $max_da.....
Так что я не смотрю петлю в начале дня, а через два часа. День все тот же, и я все еще получаю правильные значения, так как я явно запрашиваю в базе данных записи между 00:00:00 и 23:59:59 дня, независимо от фактического времени метки времени. И когда время прыгает на час, я все еще в правильном дне.
Примечание: я знаю, что это 5-летняя ветка, и я знаю, что это не ответ на вопрос ОП, но он может помочь таким людям, как я, которые сталкивались с этой страницей, в поисках решения описанной мной проблемы.
Если вам интересно, как обрабатывать EST / EDT во время преобразования. Это может помочь:
SELECT convert_tz('2021-04-06 02:06:00','US/Eastern','UTC');
Или наоборот,
SELECT convert_tz('2021-04-06 06:06:00','UTC','US/Eastern');