ЧАСОВ КЛАПТО БЕЗ ЧАСОВА ЗОНА, ИНТЕРВАЛ и лятно часово време

Работя върху приложение на Rails, което съхранява всички дати в PostgreSQL като „TIMESTAMP WITHOUT TIME ZONE“. (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.06.2015 г. в 10:00 (Европа/Берлин). Но сега е лято, така че се прилага лятно часово време и 10:00 в „Европа/Берлин“ вече е 08:00 в UTC.

Поради това намирането на всички проекти, чиято продължителност е изтекла чрез използване на следната заявка, не работи за проекти, които започват/завършват извън границите на DST:

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()

(BTW, можете също да съхранявате продължителността във вашата база данни, но е излишно, тъй като може да се изчисли от началния час и крайния час)

person joshua.paling    schedule 19.05.2015
comment
Това със сигурност е моят план Б. Но не трябва ли да е възможно - на слоя база данни - да вземем време БЕЗ ЧАСОВА ЗОНА, да присвоим неявната часова зона UTC, да я преобразуваме в Европа/Берлин, да добавим интервала и след това да я преобразуваме обратно в UTC? - person svoop; 21.05.2015
comment
- 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, зачитайки промените на DST на дадена часова зона. И 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