Mysql: Конвертировать БД из локального времени в UTC

Мне нужно преобразовать существующие (datetime fields) db из локального времени UTC.

Значения хранятся на сервере с часовым поясом CET (+1) (с летним временем +2). При выборе данных я использую UNIX_TIMESTAMP(), который магически компенсирует все, т.е. Сдвиг часового пояса и dst (если я правильно прочитал документы).

Я перемещаю db на новый сервер с UTC как системное время.

Простое вычитание -1 H не будет работать, так как летнее время равно +2.

Любые идеи для умного способа сделать это? (используя sql или некоторый script lang)

Ответ 1

Сначала вам нужно убедиться, что таблица mysql.time_zone_name заполнена. Если он пуст, вы можете выполнить инструкции на этой странице, чтобы заполнить его:

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Это обычно так же просто, как запуск такой команды в оболочке:

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

После заполнения этой таблицы вы можете использовать функцию CONVERT_TZ() для обновления существующих значений в БД:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

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

mysql> SELECT CONVERT_TZ('2010-01-22 12:00:00','CET','UTC');
+-----------------------------------------------+
| CONVERT_TZ('2010-01-22 12:00:00','CET','UTC') |
+-----------------------------------------------+
| 2010-01-22 11:00:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2010-07-22 12:00:00','CET','UTC');
+-----------------------------------------------+
| CONVERT_TZ('2010-07-22 12:00:00','CET','UTC') |
+-----------------------------------------------+
| 2010-07-22 10:00:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

Ответ 2

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

Изменение определений часового пояса. Это человеческое определение того, как отклоняться от "солнечных часов", и эти определения могут и постоянно меняться. Поэтому единственное допустимое преобразование - это даты в прошлом, потому что это больше не изменится.

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

Простой пример: позвольте создать встречу в следующем году в Берлине, Германия. Сегодня мы согласны с тем, что мы хотим встретиться в 12:00 1 июля 2014 года на Александерплац. Эта дата будет переведена в 10:00 UTC в этот день.

Теперь, если какое-то правительство решит отказаться от летнего времени в 2014 году, у вас возникнет проблема с тем, следует ли вам появляться в 12:00 по местному времени или в 11:00 по местному времени, потому что переход от UTC приведет к другому местному времени.

Если вы сохранили исходную дату "2014-07-01 12:00 Европа/Берлин", вы будете там в это точное время в полдень, как и все остальные.

Ответ 3

В исходном сервере вы можете использовать одно из следующих выражений внутри запроса UPDATE:

CONVERT_TZ(your_datetime_field,'SYSTEM','UTC') CONVERT_TZ(your_datetime_field,@@global.time_zone,'UTC')

В качестве альтернативы, на целевом сервере, если вы знаете часовой пояс исходного сервера (например, "Европа/Берлин" ), вы можете использовать одно из следующих выражений:

CONVERT_TZ(your_datetime_field,'Europe/Berlin','UTC') CONVERT_TZ(your_datetime_field,'Europe/Berlin',@@global.time_zone)