Как хранить дату и время в UTC и местном часовом поясе

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

По сути, пользователи хотят иметь возможность запрашивать (по своему местному времени) данные, собранные из систем в разных часовых поясах. Но иногда они хотят знать, что данные были созданы, скажем, в 18:00 в исходной системе. Помогает, когда пользователи из разных уголков мира сообщают об одном и том же событии.

User1: What? We don't have any data for 20:00
User2: Dude, it says 20:00 right there on my screen.
User1: Wait, what timezone are you? What's the UTC-time?
User2: What is UTC? Is that something with computers?
User1: OMFG! *click*

Я ищу совета о том, как хранить данные.

Я думаю о том, чтобы хранить все даты и время в формате UTC и добавить дополнительный столбец, содержащий исходное имя часового пояса, в форме, которая позволяет мне использовать mysql CONVERT_TZ или аналог в Java. Затем приложение преобразует даты, введенные пользователем, в UTC, и я могу легко запросить базу данных. Все даты также могут быть легко преобразованы в местное время пользователя в приложении. Используя исходный столбец часового пояса, я также смог бы отобразить исходную дату и время.

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

start_time_utc datetime
start_time_tz  varchar(64)
end_time_utc   datetime
end_time_tz    varchar(64)

Я на правильном пути здесь?

Кто-нибудь, кто работал с такими данными, поделится своим опытом?

(Я буду использовать MySQL 5.5 CE)

Обновление 1

Данные будут доставляться в XML-файлах, где каждая запись имеет дату и время в каком-то местном часовом поясе. Таким образом, будет только один процесс вставки, работающий в одном месте.

После загрузки в базу данных он будет представлен в каком-либо веб-приложении пользователям в разных часовых поясах. В большинстве случаев интересующие данные также происходили из того же часового пояса, что и пользователь, просматривающий данные. В некоторых более сложных случаях использования серия событий взаимосвязана и охватывает несколько часовых поясов. Следовательно, пользователи хотят иметь возможность говорить о событиях, чтобы исследовать возможные причины/последствия в чужое время. Не UTC, не их местное время.


mysql datetime database-design timezone convert-tz
person Ronnis    schedule 09.01.2011    source источник
comment
Что касается проблемы, я считаю, что ваше решение является хорошим.   -  person Eldad Mor    schedule 10.01.2011
comment
Разве в MySQL нет DateTimeOffset, как в MSSQL (тип данных, указывающий отметку времени и ее смещение от UTC)? Если это не так, то ваше решение является ближайшим эквивалентом, отличающимся только количеством необходимых полей (1 против 2).   -  person bzlm    schedule 10.01.2011
comment
Нет, MySQL не поддерживает TIMESTAMP WITH TIMEZONE (это тип данных ANSI для этого)   -  person a_horse_with_no_name    schedule 10.01.2011
comment
@horse, я искал что-то подобное (я экспериментировал с этим в Oracle), но не смог найти ничего в MySQL. У вас есть опыт работы с данными с часовыми поясами в MySQL?   -  person Ronnis    schedule 10.01.2011
comment
нет, не знаю. К счастью, мне не нужно часто использовать MySQL.   -  person a_horse_with_no_name    schedule 10.01.2011
comment
@Ronnis, прости, что воскрешаю, но что ты здесь делал?   -  person epoch    schedule 26.01.2015
comment
@epoch, в итоге мы сохранили пары столбцов, как в моем примере.   -  person Ronnis    schedule 27.01.2015
comment
@Ronnis, спасибо, чувак, теперь мне нужно выяснить архитектуру для 500+ таблиц, если у тебя есть какие-либо предложения, мой вопрос здесь: stackoverflow.com/questions/28146521/ :)   -  person epoch    schedule 27.01.2015

Ответы (3)


arrow_upward
6
arrow_downward

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

person Kisor Biswal    schedule 20.01.2013

arrow_upward
4
arrow_downward

В мануале есть раздел как раз для этого, про отметку времени:

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

http://dev.mysql.com/doc/refman/5.5/en/timestamp.html

Таким образом, вы можете использовать: SET time_zone = timezone; на клиенте, чтобы установить часовой пояс. Тогда все запросы будут переводить метку времени в правильный часовой пояс. Не нужно делать ничего сложного в Java, кроме установки часового пояса (я думаю, это может быть даже параметр в строке подключения JDBC)

person shlomoid    schedule 10.01.2011
comment
Мне все равно нужно сохранить исходный часовой пояс, чтобы отобразить его в другом локальном часовом поясе (см. Диалог). - person Ronnis; 10.01.2011
comment
Может быть, я неправильно понял. Итак, как сказал Эльдад, сохранение исходного часового пояса в столбце - ваш единственный вариант... - person shlomoid; 11.01.2011

arrow_upward
0
arrow_downward

Вы всегда можете взять зулусское время в качестве основы для всех своих расчетов.

person Andres SK    schedule 28.09.2011