Зона недопонимания

Автор оригинала: Josh Berkus
  • Перевод
По некоторым причинам, само понятие «времени с часовым поясом» сбивает с толку многих пользователей и разработчиков приложений. Это влечет за собой появление огромного числа шероховатостей в случае когда приложения должны иметь дело с множеством часовых поясов. В конечном итоге разработчики пытаются оформить эту логику в виде специального кода внутри приложения, в результате чего неизбежно получают заслуженный геморрой с обработкой данных.

Вот некоторые распространенные ошибочные причины, которые я слышал, призывающие не использовать тип timestamp with time zone:

  • Я хочу хранить все в формате UTC;
  • Я не хочу получать несколько разных часовых поясов из запроса;
  • Мы используем специальную библиотеку для обработки часовых поясов;
  • Я не хочу тратить дисковое пространство для хранения часового пояса.


Все эти тезисы произрастают из фундаментального непонимания принципов хранения временных данных в базе данных.



Интуитивно, можно предположить, что timestampTZ в настоящее время хранится примерно как:

"2011-06-11 15:53:22 PDT"

То есть к самому времени добавляется информация о часовом поясе. Это далеко не так.

Вместо этого, все временные данные хранятся в виде значений UTC, вне зависимости от того какой тип использовался: timestamp without time zone или же timestamp with time zone. Разница состоит в процессе записи. Если тип данных подразумевает хранение информации о часовом поясе, то всякий раз при сохранении данных они автоматически преобразовываются из локального времени пользователя во время UTC. Когда пользователь запрашивает данные, они преобразовываются из UTC в местный часовой пояс пользователя.

Допустим, Джош живет в Калифорнии (часовой пояс «America / Los_Angeles»). Он добавляет такую строку в таблицу:

INSERT INTO messages ( user_id, message, left_at )
VALUES ( 3, 'Здорово браттело!', '2011-09-27 17:17:25' );


… тогда Брюс, что живет в Филадельфии (часовой пояс «Америки / New_York»), запросив данные, увидит:

user_id | 3
message | Здорово браттело!
left_at | 2011-09-27 20:17:25-04


… а Магнус, который живет в Швеции («Europe / Stockholm»), в свою очередь, получит:

user_id | 3
message | Здорово браттело!
left_at | 2011-09-28 02:17:25+02


Данные хранятся как UTC, но то, что каждый из пользователей видит, завязано на их местном времени.

Timestamp without time zone просто-напросто не производит никаких преобразований, предполагая, что все временные данные принадлежат одному часовому поясу.

Для большинства языков программирования есть смысл оставить обработку временных данных на совести сервера PostgreSQL, а не полагаться на дополнительный программный слой. Со всей ответственностью можно заявить: „Поддержка временных данных в Postgres по праву является эталонной. И в целом, надежнее и современнее, чем в библиотеках для PHP, Python или Perl“. Стоит также отметить, что PostgreSQL прекрасно справляется с проблемами перехода на летнее\зимнее время.

Что еще более важно, использование timestampTZ означает, что вам никогда не придется беспокоиться о написании кода приложения для отображения данных в часовом поясе пользователя. Вместо этого, вам надо просто установить параметр TIMEZONE для сеанса пользователя и временные данные автоматически будут отображаться в соответствующем часовом поясе.

Конечно, есть несколько годных причин не хранить информацию о часовом поясе:

  • Ваш драйвер или ORM не поддерживает временные зоны (хотя это может быть аргументом в пользу нового);
  • Ваш код должен также работать с СУБД без адекватной поддержки часовых поясов;
  • Вы собираетесь секционировать (partitioning) таблицу по колонке с датой и нуждаетесь в абсолютных значениях;
  • База данных никогда никогда не будет использоваться более чем в одном часовом поясе.


Но если ни одна из этих причин к вам не относится, то вам следует использовать тип timestampTZ.

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

Комментарии 16

    +10
    > Если тип данных подразумевает хранение информации о часовом поясе, то всякий раз при сохранении данных они автоматически преобразовываются из локального времени пользователя во время UTC.

    Implicit casting — это плохо практически всегда.

    > Со всей ответственностью можно заявить: „Поддержка временных данных в Postgres по праву является эталонной. И в целом, надежнее и современнее, чем в библиотеках для PHP, Python или Perl“

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

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

    Очень интересно посмотреть, как автор оригинала реализовал бы это на практике — в системе, где используется внешний connection pooling и ленивая инициализация сессий (в SQLAlchemy реализовано именно так — с добавлением дополнительного пула на уровне python-процесса, что позволяет использовать одно и то же соединение для обработки нескольких клиентских запросов, независимо от наличия внешнего пула соединений). А потом обосновал бы, почему такое решение будет лучше явного приведения времени к UTC перед сохранением в базу / после получения данных из базы.
      +2
      Мы для nHibernate написали перехватчик, который доступно объясняет .net runtime'у, что все DateTime являются DateTimeKind.UTC. Он срабатывает сразу после заполнения объекта данными. Так что на уровне пула можно не заморачиваться…
      +3
      «Для большинства языков программирования есть смысл оставить обработку временных данных на совести сервера PostgreSQL, а не полагаться на дополнительный программный слой.»
      Представим себе простую ситуацию: веб-сервис, пул сессий, географически распределенные пользователи. В какой зоне отдаст данные постгресс? Правильно, в зоне веб-сервиса. Которая не имеет никакого отношения к зоне пользователя.

      Ах, выставляйте timezone в каждой сессии перед выполнение запроса? А не офигеем ли? (не говоря уже о том, что это перенос логики представления данных на уровень BL, а то и DB, где ему не надо быть)
        +4
        Вопросы задал Josh'у, посмотрим что он ответит.
          0
          > there is additional pool-level python-process

          Немного не так.

          SQLAlchemy has its own connection pool inside of a running python-procces (this pool is independent from any external tools, such as pgBouncer).
          It allows a system to process multiple sequential transactions (regardless of which user they have been committed by) with any connection which is currently at the pool.
            +2
            > So why do you think, they said, that it would be better to store timestamps with TZ then explicit time casting to UTC before storing in the DB

            И наш посыл скорее такой — относитесь к timestamp так, как предписано его именем — как к «моменту времени».

            Представьте, что вы хотите отметить точку на оси координат, на которую смотрит произвольный наблюдатель.
            Что вам будет лучше — взять за систему отсчета саму ось и отметить на ней локальную, относительно самой оси, точку (вариант с timestamp without time zone, в который записываются только UTC-моменты) или взять за систему отсчета множество возможных положений наблюдателя и отмечать на оси координат смещённое положение, в надежде, что наблюдатель в следующий раз придет со своей линейкой и заблаговременно сделает нужные преобразования (вариант с timestamp with time zone и указанием TIMESTAMP-директивы в соединении).
            0
            А в чем недостаток хранения времени в формате unix time? Насколько я знаю, практически все языки программирования умеют с ним работать. А преобразование в человекочитаемый вид вполне легко происходит с учетом таймзоны на конечном клиенте.
              +2
              для временных типов есть удобные функции и операторы, которые для unizx timestamp работать не будут
              +4
              > Поддержка временных данных в Postgres по праву является эталонной.
              Вот с этим я согласен. Когда впервые столкнулся с датами в постгресе, они мне показались сильно запутанными, кроме того, помня старый геморрой после дат в мускуле, решил сделать так как сделал бы в мускуле — просто хранить unix timestamp как int.
              Потом, конечно, огреб — все что база могла бы сделать автоматом за меня мне пришлось делать руками. Проблемы, конечно, это не составило, но время потратить пришлось.

              Сейчас использую Pg timestamp`ы — всем устраивают, удобно работать.
                0
                ИМХО крайне вредная рекомендация. Время с переходом на летнеее-зимнее не является непрерывным, что автоматически ломает любые расчеты вне базы с интервалами. Локальное время интересно только конечному пользователю и только ему и надо его показывать. А все операции — в едином и неделимом UTC и базе данных тут влезать нечего.
                  0
                  операции с датами postgresql делает в UTC. Изменяется только отображение.

                  А вот с пулом коннектов (как делает, например, pgbouncer) начинаются проблемы
                  0
                  ну что, стрелки у всех нормально перевелись? :)
                    0
                    Не совсем, тут вот жалуются некоторые ;) www.facebook.com/groups/postgresql/permalink/281632582033496/
                      0
                      ага видел, а ещё есть примеры?
                        0
                        В первый раз с таким столкнулся. Логично было бы написать на PostgreSQL Hackers <pgsql-hackers@postgresql.org> с вопросом, ИМХО
                    0
                    В первый раз с таким столкнулся. Логично было бы написать на PostgreSQL Hackers <pgsql-hackers@postgresql.org> с вопросом, ИМХО
                    UPD Промахнулся веткой.

                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                    Самое читаемое