Должен ли MySQL установить часовой пояс в UTC?

Последующий вопрос о https://serverfault.com/info/191331/should-servers-have-their-timezone-set-to-gmt-utc

Если часовой пояс MySQL установлен в UTC или должен быть установлен в тот же часовой пояс, что и сервер или PHP? (Если это не UTC)

Каковы плюсы и минусы?

Ответы

Ответ 1

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

С другой стороны, если у вас есть контроль над часовыми поясами серверов, с которыми вы работаете, вы можете настроить все настройки на UTC внутри себя и никогда не беспокоиться о часовых поясах и DST.

Вот некоторые примечания, которые я собрал о том, как работать с часовыми поясами, как форму чит-листа для себя и других, которые могут повлиять на то, какой часовой пояс выберете для своего сервера, и как он будет хранить дату и время.

Шифрование часовых поясов MySQL

Примечания:

  • Изменение часового пояса не изменит хранимое время datetime или timestamp, но он выберет другое время и время от столбцы с меткой времени
  • UTC не использует летнее время, GMT (регион), GMT (часовой пояс) не работает (GMT также вводит в заблуждение определение секунд, поэтому был разработан UTC).
  • Предупреждение! UTC имеет секунды прыжка, они выглядят так: '2012-06-30 23:59:60' и могут быть добавлен случайным образом, с предварительным уведомлением за 6 месяцев, из-за замедления вращение Земли
  • Предупреждение! разные региональные часовые пояса могут вызывать одинаковое значение даты и времени из-за на летнее время
  • Столбец с меткой времени поддерживает только даты 1970-01-01 00:00:01 до 2038-01-19 03:14:07 UTC

  • Внутри Столбец timestamp MySQL сохраняется как UTC но при выборе даты MySQL автоматически преобразует ее в текущий часовой пояс сеанса.

    При сохранении даты в метке времени MySQL будет считать, что дата находится в текущем часовом поясе сессии и конвертирует его в UTC для хранения.

  • MySQL может хранить частичные даты в столбцах datetime, они выглядят как "2013-00-00 04:00:00"
  • MySQL хранит "0000-00-00 00:00:00", если вы установите столбец datetime как NULL, если вы специально не задали для столбца значение null, когда вы создайте его.
  • Прочтите это

Чтобы выбрать столбец временной метки в формате UTC

независимо от того, в какой временной зоне находится текущий сеанс MySQL:

SELECT 
CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime` 
FROM `table_name`

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

SELECT `timestamp_field` FROM `table_name`

Чтобы выбрать текущее время в UTC:

SELECT UTC_TIMESTAMP();
SELECT UTC_TIMESTAMP;
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');

Результат: 2015-03-24 17:02:41

Чтобы выбрать текущее время и время в часовом поясе сеанса

SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP();

Чтобы выбрать часовой пояс, который был установлен при запуске сервера

SELECT @@system_time_zone;

Возвращает "MSK" или "+04: 00" для московского времени, например, есть (или была) ошибка MySQL, где, если она установлена ​​на числовое смещение, она не будет корректировать переход на летнее время

Чтобы получить текущий часовой пояс

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

Он вернется в 02:00:00, если ваш часовой пояс равен +2: 00.

Чтобы получить текущую временную метку UNIX (в секундах):

SELECT UNIX_TIMESTAMP(NOW());
SELECT UNIX_TIMESTAMP();

Чтобы получить столбец timestamp как отметку времени UNIX

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`

Чтобы получить столбец datetime UTC как временную метку UNIX

SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`

Получить текущий часовой пояс datetime с положительной метки времени UNIX

SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`

Получить UTC datetime из отметки времени UNIX

SELECT CONVERT_TZ(FROM_UNIXTIME(`unix_timestamp_int`), @@session.time_zone, '+00:00') 
FROM `table_name`

Получить текущее время и время часового пояса от отрицательного UNIX timestamp integer

SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND)

Есть 3 места, где временной интервал может быть установлен в MySQL:

Примечание. Часовой пояс можно установить в двух форматах:

  • смещение от UTC: '+00: 00', '+10: 00' или '-6: 00'
  • как названный часовой пояс: "Европа/Хельсинки", "США/Восток" или "МЕТ"

Именованные часовые пояса могут использоваться только в том случае, если таблицы информации о часовом поясе в базе данных mysql были созданы и заполнены.

в файле "my.cnf" 

default_time_zone='+00:00'

или

timezone='UTC'

@@global.time_zone variable

Чтобы узнать, на какое значение они установлены на

SELECT @@global.time_zone;

Чтобы установить для него значение, используйте один из них:

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone='+00:00';

@@session.time_zone variable

SELECT @@session.time_zone;

Чтобы установить его, используйте один из них:

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

как "@@global.time_zone variable", так и "@@session.time_zone variable" могут возвращать "SYSTEM", что означает, что они используют часовой пояс, установленный в "my.cnf".

Для того, чтобы названия часовых поясов работали (даже для зоны по умолчанию-времени), вы должны настроить таблицы информации о часовом поясе, которые необходимо заполнить: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Примечание: вы не можете сделать это, так как он вернет NULL:

SELECT 
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime` 
FROM `table_name`

Настройка таблиц часовых поясов mysql

Для работы CONVERT_TZ вам понадобятся таблицы часовых поясов, которые будут заполнены

SELECT * FROM mysql.`time_zone` ;
SELECT * FROM mysql.`time_zone_leap_second` ;
SELECT * FROM mysql.`time_zone_name` ;
SELECT * FROM mysql.`time_zone_transition` ;
SELECT * FROM mysql.`time_zone_transition_type` ;

Если они пусты, заполните их, выполнив эту команду

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

если эта команда дает вам ошибку "данные слишком долго для сокращения абзаца столбца в строке 1 ", то это может быть вызвано NULL-символ добавляется в конце аббревиатуры часового пояса

Исправление состоит в том, чтобы запустить этот

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
(if the above gives error "data too long for column 'abbreviation' at row 1")
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql

echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql
cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql

mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql

(убедитесь, что ваши правила dst для серверов обновлены zdump -v Europe/Moscow | grep 2011 https://chrisjean.com/updating-daylight-saving-time-on-linux/)

См. полную историю перехода DST (переход на летнее время) для каждого часового пояса

SELECT 
tzn.Name AS tz_name,
tztt.Abbreviation AS tz_abbr,
tztt.Is_DST AS is_dst,
tztt.`Offset` AS `offset`,
DATE_ADD('1970-01-01 00:00:00',INTERVAL tzt.Transition_time SECOND)  AS transition_date
FROM mysql.`time_zone_transition` tzt
INNER JOIN mysql.`time_zone_transition_type` tztt USING(Time_zone_id, Transition_type_id)
INNER JOIN mysql.`time_zone_name` tzn USING(Time_zone_id)
-- WHERE tzn.Name LIKE 'Europe/Moscow' -- Moscow has weird DST changes
ORDER BY tzt.Transition_time ASC

CONVERT_TZ также применяет любые необходимые изменения DST на основе правил в приведенных выше таблицах и даты, которую вы используете.

Примечание:
Согласно docs, значение, которое вы задали для time_zone, не изменяется, если вы установите его как "+01: 00", например, time_zone будет устанавливаться как смещение от UTC, которое не следует за DST, поэтому оно будет оставаться неизменным круглый год.

Только имя timezones изменяет время в течение летнего времени.

Сокращения, такие как CET, всегда будут зимним временем, а CEST будет летним, тогда как +01: 00 всегда будет UTC время + 1 час, и оба они не изменятся с помощью DST.

Часовой пояс system будет часовым поясом хост-машины, на которой установлена ​​mysql (если mysql не может ее определить)

Вы можете больше узнать о работе с DST здесь

связанные вопросы:

Источники:

Ответ 2

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

Прочтите этот учебник: Как синхронизировать ваши часовые пояса PHP и MySQL

Ответ 3

Плюсы и минусы в значительной степени идентичны. Это зависит от того, хотите вы этого или нет.

Будьте осторожны, если временная зона MySQL отличается от вашего системного времени (например, PHP), сравнение времени или печати с пользователем потребует некоторого вмешательства.