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)
