TIMESTAMP БЕЗ ЧАСОВОГО ПОЯСА, ИНТЕРВАЛОВ и летнего времени.

Я работаю над приложением Rails, которое хранит все даты в PostgreSQL как «TIMESTAMP БЕЗ ЧАСОВОГО ПОЯСА». (Rails обрабатывает часовой пояс на уровне приложения, которым для этого приложения является «Европа / Берлин».) К сожалению, переход на летнее время (DST) становится проблемой.

В упрощенной таблице «проекты» есть следующие столбцы:

started_at TIMESTAMP WITHOUT TIME ZONE
duration INTEGER

Проекты начинаются с started_at и выполняются duration дней.

Теперь предположим, что есть только один проект, который начинается 01.01.2015 в 10:00. Поскольку сейчас "Европа / Берлин" и сейчас январь (без летнего времени), запись в базе данных выглядит следующим образом:

SET TimeZone = 'UTC';
SELECT started_at from projects;
# => 2015-01-01 09:00:00

Он должен закончиться 30 июня 2015 года в 10:00 (Европа / Берлин). Но сейчас лето, поэтому применяется летнее время, и 10:00 в «Европе / Берлине» теперь 08:00 по всемирному координированному времени.

Из-за этого поиск всех проектов, для которых истек срок действия, с помощью следующего запроса не работает для проектов, которые начинаются / заканчиваются за пределами границ летнего времени:

SELECT * FROM projects WHERE started_at + INTERVAL '1 day' * duration < NOW()

Я предполагаю, что было бы лучше, если бы указанное выше WHERE выполняло расчет в часовом поясе «Европа / Берлин», а не в «UTC». Я пробовал несколько вещей с ::TIMESTAMTZ и AT TIME ZONE, ни один из них не работал.

В качестве примечания: согласно документам PostgreSQL, + INTERVAL должен работать с интервалами «1 день» иначе, чем с интервалами «24 часа», когда дело касается летнего времени. При добавлении дней летнее время игнорируется, поэтому 10:00 всегда остается 10:00. С другой стороны, при добавлении часов 10:00 может стать 09:00 или 11:00, если вы тем или иным образом пересекаете границу летнего времени.

Большое спасибо за любые подсказки!


postgresql ruby-on-rails intervals postgresql-9.4 dst
person svoop    schedule 19.05.2015    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Думаю, у вас есть две стратегии, чтобы избежать головной боли:

  1. Пусть Rails обрабатывает все, что связано с часовыми поясами, чтобы Postgres вообще не занимался этим.

or

  1. Пусть Postgres обрабатывает все, что связано с часовыми поясами, чтобы Rails вообще не занимался этим.

Их смешивание всегда будет болезненным, и в основном это то, что вызывает сейчас ваши проблемы. Я бы выбрал стратегию 1 (пусть с этим справится Rails). Для этого ваша база данных Postgres должна хранить время начала и время окончания в формате UTC. duration может все еще присутствовать в вашем пользовательском интерфейсе, но если пользователь вводит время начала и продолжительность, вы должны рассчитать время окончания и сохранить это время в своей базе данных. Время начала, которое вводят пользователи, и время окончания, которое вы рассчитываете в своем приложении, зависят от часового пояса, и вы просто позволяете Rails обрабатывать преобразование в UTC при сохранении в базе данных.

Тогда ваш запрос будет просто:

SELECT * FROM projects WHERE finished_at < NOW()

(Кстати, вы также можете сохранить продолжительность в своей базе данных, но это излишне, поскольку ее можно рассчитать по времени начала и времени окончания)

person joshua.paling    schedule 19.05.2015
comment
Это, конечно, мой план Б. Но разве это не должно быть возможным - на уровне базы данных - взять время БЕЗ ЧАСОВОГО ПОЯСА, назначить неявный часовой пояс UTC, преобразовать его в Европу / Берлин, добавить интервал и затем преобразовать его обратно в УНИВЕРСАЛЬНОЕ ГЛОБАЛЬНОЕ ВРЕМЯ? - person svoop; 21.05.2015
comment
ТБХ Я не уверен, возможно это или нет. По моему опыту, такие вычисления намного проще выполнять вне SQL, даже если они возможны. - person joshua.paling; 22.05.2015
comment
Наконец-то я нашел способ производить вычисления на уровне базы данных. Тем не менее, я в любом случае поддержу ваш ответ, поскольку для нового приложения это, безусловно, действительная альтернатива хранению started_at и ended_at, а не duration, а затем выполнение всех математических расчетов на уровне приложения. - person svoop; 28.05.2015

arrow_upward
0
arrow_downward

Я создал функцию, которая вычисляет ended_at, добавляя duration дней к started_at с учетом изменений летнего времени в данном часовом поясе. И started_at, и ended_at, однако, находятся в формате UTC и поэтому хорошо работают с Rails.

Он превращает started_at (метка времени без часового пояса, неявное UTC в Rails) в метку времени с часовым поясом UTC, затем в указанный часовой пояс, добавляет duration и возвращает метку времени без часового пояса (неявное UTC).

# ended_at(started_at, duration, time_zone)
CREATE FUNCTION ended_at(timestamp, integer, text = 'Europe/Zurich') RETURNS timestamp AS $$
  SELECT (($1::timestamp AT TIME ZONE 'UTC' AT TIME ZONE $3 + INTERVAL '1 day' * $2) AT TIME ZONE $3)::timestamp
$$  LANGUAGE SQL IMMUTABLE SET search_path = public, pg_temp;

С помощью этой функции я могу избавиться от необходимости добавлять ended_at в качестве явного столбца, который необходимо синхронизировать. И пользоваться им просто:

SELECT ended_at(started_at, duration) FROM projects
person svoop    schedule 28.05.2015