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

Я понял это для своих целей. Я подытожу то, что я узнал (извините, эти заметки многословны; они так же предназначены для моего будущего направления, как и все остальное).

вопреки тому, что я сказал в одном из моих предыдущих комментариев, поля DATETIME и TIMESTAMP do вести себя по-другому. Поля TIMESTAMP (как указано в документах) принимают все, что вы отправляете им в формате "YYYY-MM-DD hh:mm:ss", и преобразуют его из текущего часового пояса в время UTC. Происходит обратное прозрачно всякий раз, когда вы получаете данные. Поля DATETIME не выполняют это преобразование. Они берут все, что вы им посылаете, и просто хранят его напрямую.

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

хорошо, поэтому мы должны хранить наши данные в не DST часовой пояс (например, UTC). Поля TIMESTAMP не могут точно обрабатывать эти данные по причинам, которые я объясню: если ваша система настроена на часовой пояс DST, то то, что вы помещаете в TIMESTAMP, может быть не тем, что вы получаете обратно. Даже если вы отправите ему данные, которые вы уже преобразовали в UTC, он все равно будет считать, что данные находятся в вашем локальном часовом поясе и сделать еще одно преобразование в UTC. Эта метка-действие локального до мирового возвращению в местный и обратно с потерями, когда местный часовой пояс наблюдает летнее время (с "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. Для этого я использую функцию strtotime() PHP или ее класс DateTime. Это не может быть надежно сделано внутри MySQL, используя функции CONVERT_TZ() или UNIX_TIMESTAMP () MySQL, потому что 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.

Фух. Большое спасибо за вклад и помощь каждого. Обнадеживающе это избавляет кого-то еще от головной боли в будущем.

кстати, я вижу это на 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 столбцы с On UPDATE CURRENT_TIMESTAMP (т. е.: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) для отслеживания измененных записей и ETL в datawarehouse.

в случае, если кто-то задается вопросом, в этом случае,TIMESTAMP вести себя правильно, и вы можете различать между двумя похожими датами путем преобразования TIMESTAMP в unix timestamp:

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 было дважды извлечено из базы данных и передано флоту, поэтому каждый день после этой даты был перемещен на один день вправо.
После того, как Ищу ошибка в моем коде на некоторое время я понял, что эта дата, когда происходит 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( "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-летний поток, и я знаю, что это не ответ на вопрос OPs, но это может помочь таким людям, как я, которые столкнулись с этой страницей в поисках решения проблемы, которую я описал.