Поля MySQL datetime и летнее время – как я могу ссылаться на «дополнительный» час?

Типы дат 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 все равно будет хранить и возвращать (в строчной форме только!) 2009-03-08 02:59:59 в течение «потерянного» часа, когда начинается 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 

Вынос: если вам нужно хранить и извлекать каждый раз в году, у вас есть несколько нежелательных вариантов:

1. Установите системный часовой пояс на GMT + некоторое постоянное смещение. Например, UTC

2. Хранить даты как INT (как обнаружил Аарон, TIMESTAMP не является даже надежным)

3. Притвориться, что тип 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 (как указывают документы) берут все, что вы отправляете в формате «ГГГГ-ММ-ДД чч: мм: ss», и конвертируете их из текущего часового пояса в UTC. Обратное происходит прозрачно всякий раз, когда вы извлекаете данные. Поля DATETIME не делают этого преобразования. Они берут все, что вы им отправляете, и просто храните их прямо.

Ни типы DATETIME, ни типы TIMESTAMP не могут точно хранить данные в часовом поясе, который наблюдает за DST . Если вы сохраняете «2009-11-01 01:30:00», поля не имеют возможности различать, какая версия 1:30 вам нужна – версия -04: 00 или -05: 00.

Итак, мы должны хранить наши данные в часовом поясе, отличном от DST (например, UTC). Поля TIMESTAMP не могут точно обрабатывать эти данные по причинам, которые я объясню: если ваша система настроена на часовой пояс DST, то, что вы вкладываете в TIMESTAMP, возможно, не из того, что вы получаете. Даже если вы отправляете данные, которые вы уже конвертировали в UTC, они все равно будут считать данные в вашем локальном часовом поясе и сделать еще одно преобразование в UTC. Этот TIMESTAMP-принудительный переход между локальными и UTC-обратными к местным является потерями, когда ваш местный часовой пояс наблюдает за DST (поскольку «2009-11-01 01:30:00» отображает до двух разных возможных времен).

С 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. Для этого я использую функцию strtotime () PHP или ее класс DateTime. Он не может быть надежно выполнен внутри MySQL с использованием функций MySQL CONVERT_TZ () или UNIX_TIMESTAMP (), потому что CONVERT_TZ выдаст только значение «YYYY-MM-DD hh: mm: ss», которое страдает от проблем с неоднозначностью, а UNIX_TIMESTAMP () принимает входной сигнал находится в системном часовом поясе, а не в часовом поясе, в который данные были ВКЛЮЧЕНЫ в (UTC).

При хранении данных в базе данных:

Преобразуйте свою дату в точное время UTC, которое вы хотите за пределами MySQL. Например: с помощью класса DateTime PHP вы можете указать «2009-11-01 1:30:00 EST» в отличие от «2009-11-01 1:30:00 EDT», а затем преобразовать его в UTC и сохранить правильное время UTC в поле DATETIME.

Уф. Большое спасибо за вклад и помощь каждого. Надеюсь, это спасет кого-то еще от головной боли по дороге.

BTW, я вижу это в 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 в localtime, используя CONVERT_TZ :

 SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM'); 

Где «+00: 00» – это UTC, часовой пояс и «SYSTEM» – это местный часовой пояс для ОС, где работает MySQL.

Этот поток сделал меня уродством, так как мы используем столбцы 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). Я заполнил таблицу тестовыми данными, и все выглядело отлично, но я заметил, что в конце графика точки были в один выходной день в соответствии с метками на оси х. После осмотра я заметил, что количество просмотров на день 2015-10-25 было дважды извлечено из базы данных и передано Флоту, поэтому каждый день после этой даты был перемещен на один день вправо.
После поиска ошибки в моем коде какое-то время я понял, что эта дата, когда DST имеет место. Затем я пришел на эту страницу 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 

Затем в цикле for, начиная с 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( "Ymd 00:00:00", $day ) . "' AND '" . date( "Ymd 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-летний поток, и я знаю, что это не ответ на вопрос OPs, но он может помочь таким людям, как я, столкнулся с этой страницей, ища решение проблемы, которую я описал.