Как MySQL обрабатывает летнее время [дубликат]

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

Вопреки тому, что я сказал в одном из моих предыдущих комментариев, 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. Этот временной интервал с временным интервалом между локальными и UTC-ответами TIMESTAMP является убыточным, когда ваш часовой пояс наблюдает за DST (поскольку «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.

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

Кстати, я вижу это в MySQL 5.0.22 и 5.0.27

Но как я могу ничего сохранить до 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» - локальный часовой пояс OS, в котором работает MySQL.

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

Этот поток сделал меня freak, так как мы используем колонки TIMESTAMP с On UPDATE CURRENT_TIMESTAMP (т. е. recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) для отслеживания измененных записей и ETL в хранилище данных.

В случае, если кому-то интересно, в этом case 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