PHP, MySQL и часовые пояса

Этот ответ был обновлен, чтобы удовлетворить щедрость. Исходный, неотредактированный ответ находится ниже строки.

Почти все вопросы, добавленные владельцем баунти, связаны с тем, как MySQL и PHP должны взаимодействовать в контексте часовых поясов.

MySQL по-прежнему имеет жалкую поддержку часового пояса , а это значит, что интеллект должен быть PHP-стороной.

• Установите часовой пояс соединения с MySQL в UTC, как описано в ссылке выше. Это приведет к тому, что все данные, обрабатываемые MySQL, включая NOW() , будут обрабатываться безопасно.
• Всегда используйте DATETIME , никогда не используйте TIMESTAMP если вы явно не требуете особого поведения в TIMESTAMP . Это менее болезненно, чем раньше.
  • Это нормально хранить время эпохи Unix как целое, если вам нужно , например, для устаревших целей. Эпоха – это UTC.
  • Предпочтительный формат даты и времени MySQL создается с использованием строки формата даты PHP Ymd H:i:s
• Преобразование всех дат времени PHP в UTC при их хранении в MySQL, что является тривиальной вещью, как показано ниже
• Datetimes, возвращаемые из MySQL, могут безопасно передаваться конструктору PHP DateTime. Обязательно перейдите в часовой пояс UTC!
• Преобразуйте PHP DateTime в локальный часовой пояс пользователя на эхо , как только. К счастью, сравнение DateTime и математика с другими DateTimes будет учитывать часовой пояс, в котором каждый находится.
• Вы все еще придерживаетесь капризов базы данных DST, поставляемой с PHP. Обновляйте обновления для PHP и OS! Храните MySQL в блаженном состоянии UTC, чтобы удалить одно потенциальное раздражение DST.

Это касается большинства пунктов.

Последнее, что делает doozy:

• Что делать, если они ранее вставили данные (например, с помощью NOW() ), не беспокоясь о часовом поясе, чтобы убедиться, что все остается неизменным?

Это настоящая досада. Один из других ответов указал на CONVERT_TZ MySQL, хотя я лично сделал это, перепрыгивая между временными CONVERT_TZ между сервером и UTC во время выбора и обновлений, потому что я так хардкор.

приложение также должно иметь возможность устанавливать / выбирать DST соответственно для каждого пользователя.

Вам не нужно и не следует делать это в современную эпоху.

Современные версии PHP имеют класс DateTimeZone , который включает в себя возможность перечислить именованные часовые пояса . Именованные часовые пояса позволяют пользователю выбирать свое фактическое местоположение и система автоматически определяет свои правила DST на основе этого местоположения.

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

 // UTC default date_default_timezone_set('UTC'); // Note the lack of time zone specified with this timestamp. $nowish = new DateTime('2011-04-23 21:44:00'); echo $nowish->format('Ymd H:i:s'); // 2011-04-23 21:44:00 // Let's pretend we're on the US west coast. // This will be PDT right now, UTC-7 $la = new DateTimeZone('America/Los_Angeles'); // Update the DateTime's timezone... $nowish->setTimeZone($la); // and show the result echo $nowish->format('Ymd H:i:s'); // 2011-04-23 14:44:00 

Используя этот метод, система автоматически выберет правильные настройки DST для пользователя, не спрашивая у пользователя, есть ли в настоящее время DST.

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

 $dt = new DateTime('now', new DateTimeZone('UTC')); foreach(DateTimeZone::listIdentifiers() as $tz) { $dt->setTimeZone(new DateTimeZone($tz)); echo $tz, ': ', $dt->format('Ymd H:i:s'), "\n"; } 

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

Давайте сравним этот метод с тем, чтобы сделать это самостоятельно. Вам нужно будет фактически выполнять математику по дате каждый раз, когда вы манипулируете временем datetime, в дополнение к тому, чтобы оттолкнуть пользователя от пользователя, которого они действительно не заботятся. Это не просто субоптимально, это бат-гуано безумный. Заставляя пользователей обозначать, когда они хотят, чтобы поддержка DST вызывала проблемы и путаницу.

Кроме того, если вы хотите использовать современные PHP DateTime и DateTimeZone для этого, вам нужно будет использовать устаревшие строки часовых поясов Etc/GMT... вместо названных часовых поясов. Эти имена зон могут быть удалены из будущих версий PHP, поэтому было бы неразумно это делать. Я говорю все это по опыту.

tl; dr : Используйте современный набор инструментов, избавитесь от ужасов математики. Представьте пользователю список названных часовых поясов. Сохраните ваши даты в UTC , на которые DST никак не повлияет. Преобразовать данные в выбранный вами часовой пояс пользователя, а не ранее.

В соответствии с запросом, вот петля над доступными часовыми поясами, отображающая их смещение GMT ​​в минутах. Я отобрал здесь несколько минут, чтобы продемонстрировать несчастливый факт: не все смещения – целые часы! Некоторые фактически переключаются на полчаса вперед во время ДСТ вместо целого часа. Получаемое смещение в минутах должно совпадать с результатом Date.getTimezoneOffset Javascript.

 $utc = new DateTimeZone('UTC'); $dt = new DateTime('now', $utc); foreach(DateTimeZone::listIdentifiers() as $tz) { $local = new DateTimeZone($tz); $dt->setTimeZone($local); $offset = $local->getOffset($dt); // Yeah, really. echo $tz, ': ', $dt->format('Ymd H:i:s'), ', offset = ', ($offset / 60), " minutes\n"; } 

• Как сохранить время в базе данных из объекта PHP DateTime

  В стандарте SQL-92 указано, что временные литералы должны быть переданы в SQL с использованием подходящего ключевого слова типа данных (например, TIMESTAMP для значений даты / времени), за которым следует строковое представление значения (содержащее необязательное смещение часового пояса, если не по умолчанию).

  К сожалению, MySQL не соответствует этой части стандарта SQL. Как описано в литературе даты и времени :

  Стандартный SQL позволяет указать временные литералы с использованием ключевого слова type и строки.

    [ удаление ] 

  MySQL распознает эти конструкции, а также соответствующий синтаксис ODBC:

    [ удаление ] 

  Однако MySQL игнорирует ключевое слово type, и каждая из предыдущих конструкций создает строковое значение ' str ' с типом VARCHAR .

  В документации описываются литеральные форматы, поддерживаемые MySQL и, в частности, явные смещения часовых поясов. Есть запрос функции, чтобы исправить это, которому сейчас более семи лет, и который вряд ли будет представлен в ближайшее время.

  Вместо этого необходимо установить переменную time_zone до обмена значениями даты и времени между сервером и клиентом. Поэтому, используя PDO :

  1. Подключение к MySQL:

      $dbh = new PDO("mysql:dbname=$dbname", $username, $password); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); 

  2. Установите для сеанса time_zone для объекта DateTime :

      $qry = $dbh->prepare('SET SESSION time_zone = ?'); $qry->execute([$datetime->format('P')]); 

  3. Создайте подходящий литерал из объекта DateTime и перейдите в MySQL как обычно (т.е. в качестве параметра в подготовленный оператор).

     Как описано в документации, существует ряд возможных литералов, которые можно использовать. Тем не менее, я бы предложил использовать строку в 'YYYY-MM-DD hh:mm:ss.ffffff' (обратите внимание, что дробные секунды будут игнорироваться в версиях MySQL до 5.6), так как она наиболее близка к SQL стандарт; действительно, можно было бы префикс литерала с ключевым словом TIMESTAMP чтобы убедиться, что его SQL переносится:

      $qry = $dbh->prepare(' UPDATE my_table SET the_time = TIMESTAMP ? WHERE ... '); $qry->execute([$datetime->format('Ymd H:i:s.u')]); 

• Должны ли они храниться в DATETIME или TIMESTAMP ? Каковы преимущества или оговорки для каждого?

  PHP DateTime объекты всегда должны храниться в столбцах типа TIMESTAMP .

  Самое принципиальное различие заключается в том, что TIMESTAMP хранит информацию о часовом поясе (путем сохранения значения в формате UTC и преобразования в / из в соответствии с time_zone переменной time_zone выше), тогда как DATETIME этого не делает. Таким образом, TIMESTAMP полезен для представления определенного момента времени (аналогично объектам PHP DateTime ), тогда как DATETIME полезно для представления времени, которое отображается в календаре / часах (как на фотографии).

  Как DATETIME DATE , DATETIME и TIMESTAMP :

  Тип DATETIME используется для значений, содержащих как дату, так и время. MySQL извлекает и отображает значения DATETIME в 'YYYY-MM-DD HH:MM:SS' . Поддерживаемый диапазон: '1000-01-01 00:00:00' до '9999-12-31 23:59:59' .

  Тип данных TIMESTAMP используется для значений, содержащих как дату, так и время. TIMESTAMP имеет диапазон '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

  MySQL преобразует значения TIMESTAMP из текущего часового пояса в UTC для хранения и обратно из UTC в текущий часовой пояс для извлечения. (Этого не происходит для других типов, таких как DATETIME .) По умолчанию текущий часовой пояс для каждого соединения – это время сервера. Часовой пояс можно установить для каждого соединения. Пока настройка часового пояса остается постоянной, вы возвращаете то же самое значение, которое вы храните. Если вы сохраняете значение TIMESTAMP , а затем изменяете часовой пояс и извлекаете значение, полученное значение отличается от сохраненного вами значения. Это происходит потому, что тот же часовой пояс не использовался для преобразования в обоих направлениях. Текущий часовой пояс доступен как значение системной переменной time_zone . Для получения дополнительной информации см. Раздел 10.6 «Поддержка часовых поясов сервера MySQL» .

  Тип данных TIMESTAMP предлагает автоматическую инициализацию и обновление до текущей даты и времени. Для получения дополнительной информации см. Раздел 11.3.5 «Автоматическая инициализация и обновление для TIMESTAMP » .

  Обратите внимание на заключительный абзац, который часто ловит новичков в MySQL.

  Также может быть полезно добавить, что, как DATETIME Требованиях хранения данных типа данных , значения DATETIME требуют 8 байтов для хранения, тогда как значения TIMESTAMP требуют только 4 байта (базовый формат хранения данных можно найти в представлении типа данных даты и времени ).

• Нужно ли нам беспокоиться о часовых поясах с MySQL DATE ?

  В течение некоторого времени важно быть чувствительным к часовому поясу. По определению, сама дата сама по себе одинакова независимо от своего часового пояса, и поэтому при использовании типа данных DATE MySQL нет необходимости «беспокоиться о часовых поясах».

  Следствием этого является то, что если у одного есть значение, чувствительное к часовому поясу, необходимо также сохранить его время, например, в столбце TIMESTAMP : использование столбца DATE приводит к необратимой потере существенной информации.

• Как вставить значения с помощью функции NOW() . Нужно ли их каким-либо образом преобразовывать либо до, либо после вставки?

  Как описано в разделе NOW() :

  Возвращает текущую дату и время как значение в 'YYYY-MM-DD HH:MM:SS' или YYYYMMDDHHMMSS.uuuuuu , в зависимости от того, используется ли функция в строковом или числовом контексте. Значение выражается в текущем часовом поясе.

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

   INSERT INTO my_table (the_time) VALUES (NOW()); 

  Обратите внимание, что, как упоминалось выше, автоматическая инициализация MySQL столбцами TIMESTAMP делает излишним большинство попыток использовать NOW() во время вставки / обновления записи.

• Нужно ли устанавливать часовой пояс, используемый MySQL? Если да, то как? Должно ли это выполняться настойчиво или при каждом запросе HTTP? Должен ли он быть установлен в UTC или может быть что-то еще? Или достаточно времени сервера?

  Это уже рассмотрено выше. Можно установить переменную time_zone MySQL во time_zone мире, если это необходимо, и, следовательно, не нужно устанавливать ее при каждом соединении. Дополнительную информацию см. В разделе Поддержка часовых поясов MySQL Server .

• Как получить значения из MySQL и преобразовать их в объект DateTime . Поместив его прямо в DateTime::__construct() хватит или нам нужно использовать DateTime::createFromFormat() ?

  Как описано в Compound Formats , один из форматов даты / времени, распознаваемых парсером, который использует PHP в DateTime::__construct() является выходным форматом MySQL.

  Однако, поскольку формат вывода MySQL не включает часовой пояс, необходимо обязательно предоставить конструктору DateTime эту информацию через необязательный второй аргумент:

   $qry = $dbh->prepare('SET SESSION time_zone = ?'); $qry->execute([$timezone->getName()]); $qry = $dbh->query('SELECT the_time FROM my_table'); $datetime = new DateTime($qry->fetchColumn(), $timezone); 

  Кроме того, MySQL может преобразовать время в временную метку UNIX и построить объект DateTime :

   $qry = $dbh->query('SELECT UNIX_TIMESTAMP(the_time) FROM my_table'); $datetime = new DateTime($qry->fetchColumn()); 

• Когда нужно конвертировать в местное время и почему. Есть ли когда-нибудь время, которое мы хотели бы преобразовать, прежде чем оно будет отозвано обратно пользователю (например, для сравнения с другим объектом DateTime или статическим значением)?

  Я не уверен, что вы подразумеваете под «местным временем» (локальным для которых? RDBMS? Веб-сервером? Webclient?), Но сравнение между объектами DateTime будет обрабатывать изменения в часовом поясе по мере необходимости (PHP хранит значения внутри UTC и только конвертирует для вывода).

• Есть ли время, когда нам нужно беспокоиться о летнем времени (DST)? Почему или почему нет?

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

• Что делать, если они ранее вставили данные (например, с помощью NOW() ), не беспокоясь о часовом поясе, чтобы убедиться, что все остается неизменным?

  Как упоминалось выше, использование NOW() никогда не должно вызывать проблем.

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

   UPDATE my_table SET the_time = CONVERT_TZ(the_time, '+00:00', '+10:00'); 

Как сохранить время в базе данных из объекта PHP DateTime. Должны ли они храниться в DATETIME или TIMESTAMP? Каковы преимущества или оговорки для каждого?

* UPDATE, уточните мой первый абзац * Вы также можете сохранить временную метку как INT. Преимущество заключается в том, что вы знаете, в каком временном часе вы сохранили свое значение, поскольку временная метка – это текущее время, измеренное в секундах от эпохи Unix (1 января 1970 года 00:00:00 по Гринвичу). см. php doc: http://php.net/manual/en/function.time.php. Используя 64-битную операционную систему, вам не нужно беспокоиться о проблеме 2038 года: http://en.wikipedia.org/ вики / Year_2038_problem

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

Нужно ли нам беспокоиться о часовых поясах с MySQL DATE?

В MySQL функции CURRENT_TIMESTAMP (), CURRENT_TIME (), CURRENT_DATE () и FROM_UNIXTIME () возвращают значения в текущий часовой пояс соединения, который доступен как значение системной переменной time_zone. Кроме того, UNIX_TIMESTAMP () предполагает, что его аргумент представляет собой значение даты и времени в текущем часовом поясе. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Как вставить значения с помощью функции СЕЙЧАС (). Нужно ли их каким-либо образом преобразовывать либо до, либо после вставки?

Если вы используете временную метку, вы можете положиться на функцию PHP, это всего лишь целое число.

Если вы используете дату, функция curdate позволяет вам иметь текущую дату. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_curdate

Нужно ли устанавливать часовой пояс, используемый MySQL? Если да, то как? Должно ли это выполняться настойчиво или при каждом запросе HTTP? Должен ли он быть установлен в UTC или может быть что-то еще? Или достаточно времени сервера?

см. http://dev.mysql.com/doc/refman/5.0/ru/time-zone-support.html

Как получить значения из MySQL и преобразовать их в объект DateTime. Поместив его прямо в DateTime :: __ construct (), хватит или нам нужно использовать DateTime :: createFromFormat ()?

Опять же, если вы используете временную метку, это проще. Вы знаете временную зону timestamp, вы знаете, когда нужно конвертировать в местное время и почему. DST прост в управлении с отметкой времени, см. Функции вокруг метки времени: http://php.net/manual/en/function.mktime.php

Есть ли когда-нибудь время, которое мы хотели бы преобразовать, прежде чем оно будет отозвано обратно пользователю (например, для сравнения с другим объектом DateTime или статическим значением)?

Я снова думаю, timestamp позволяет работать с вашей датой, чтобы сравнить их, извлечь все, что вам нужно, и распечатать то, что вы хотите.

Есть ли время, когда нам нужно беспокоиться о летнем времени (DST)? Почему или почему нет? Что делать, если они ранее вставили данные (например, с помощью NOW ()), не беспокоясь о часовом поясе, чтобы убедиться, что все остается неизменным?

Да, вы должны беспокоиться о том, нужно ли вам назначать встречу или встречаться в приложении. Я разработал два приложения: один для клинического назначения и один для назначения на семинары, которые поддерживают более 70000 учетных записей и огромное количество записей. Я придерживаюсь отметки времени, очень просто индексировать, манипулировать, сравнивать. Печатная часть появляется только на экране.

Существуют преимущества использования datetime в вашей базе данных. Если вам нужно проанализировать данные из таблицы в sql direct, читать их намного проще, это более «читаемый человеком».

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