Получите "время с часовым поясом" с "время без часового пояса" и имя часового пояса

Ответ 1

ПРЕДУПРЕЖДЕНИЕ: Новичок PostgreSQL (см. комментарии к вопросу!). Я немного знаю о часовых поясах, поэтому я знаю, что имеет смысл спросить.

Мне кажется, что это в основном неподдерживаемая ситуация (к сожалению), когда дело доходит до AT TIME ZONE. Глядя на AT TIME ZONE, она дает таблицу, где только типы значений ввода:

• временная метка без часового пояса
• временная метка с часовым поясом
• время с часовым поясом

Нам не хватает того, что вы хотите: время без часового пояса. То, что вы просите, несколько логично, хотя оно зависит от даты... поскольку разные часовые пояса могут иметь разные смещения в зависимости от даты. Например, 12:00:00 Европа/Лондон может означать 12:00:00 UTC, или это может означать 11:00:00 UTC, в зависимости от того, будет ли это зимой или летом.

В моей системе, установив часовой пояс системы в America/Regina, запрос

SELECT ('2011-11-22T12:00:00'::TIMESTAMP WITHOUT TIME ZONE) 
                               AT TIME ZONE 'America/Vancouver'

дает мне 2011-11-22 14:00:00-06. Это не идеально, но он, по крайней мере, дает мгновенный момент времени (я думаю). Я считаю, что если вы выберете это с помощью клиентской библиотеки или сравните ее с другим TIMESTAMP WITH TIME ZONE, вы получите правильный результат. Это просто преобразование текста, которое затем использует системный часовой пояс для вывода.

Будет ли это достаточно хорошо для вас? Можете ли вы либо изменить поле SCHEDULES.time как поле TIMESTAMP WITHOUT TIME ZONE, либо (во время запроса) объединить время из поля с датой, чтобы создать временную метку без часового пояса?

EDIT: Если вы довольны "текущей датой", похоже, вы можете просто изменить свой запрос на:

SELECT (current_date + SCHEDULES.time) AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

Конечно, текущая системная дата может отличаться от текущей даты в локальном часовом поясе. Я думаю, что это исправит эту часть...

SELECT ((current_timestamp AT TIME ZONE USERS.tz)::DATE + schedules.time)
       AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

Другими словами:

• Возьмите текущий
• Выполните локальную дату/время в часовом поясе пользователя.
• Возьмите дату этого
• Добавьте время расписания к этой дате, чтобы получить TIMESTAMP WITHOUT TIME ZONE
• Используйте AT TIME ZONE для применения часового пояса к этой локальной дате/времени

Я уверен, что есть лучший способ, но я думаю, что это имеет смысл.

Вы должны знать, что в некоторых случаях это может потерпеть неудачу:

• Что вы хотите, чтобы результат был в течение 01:30 в день, когда часы пропускаются с 01:00 до 02:00, так что 01:30 вообще не происходит?
• Что вы хотите, чтобы результат был в течение 01:30 в день, когда часы возвращаются с 02:00 до 01:00, так что 01:30 встречается дважды?

Ответ 2

Вот демо, как рассчитать время без кастинга в текст:

CREATE TEMP TABLE schedule(t time, tz text);
INSERT INTO schedule values
 ('12:00:00', 'America/Vancouver')
,('12:00:00', 'US/Mountain')
,('12:00:00', 'America/Regina');

SELECT s.t AT TIME ZONE s.tz
        - p.utc_offset
        + EXTRACT (timezone from now()) * interval '1s'
FROM   schedule s
JOIN   pg_timezone_names p ON s.tz = p.name;

В основном вы должны вычесть смещение UTC и добавить смещение вашего локального часового пояса, чтобы добраться до данного часового пояса.

Вы можете ускорить вычисление путем жесткого кодирования вашего локального смещения. В вашем случае (Америка/Регина), который должен быть:

SELECT s.t AT TIME ZONE s.tz
        - p.utc_offset
        - interval '6h'
FROM   schedule s
JOIN   pg_timezone_names p ON s.tz = p.name;

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

Я бы сохранил аббревиатуры часового пояса и выполнил двойной листинг через text, не присоединившись к pg_timezone_names для оптимальной производительности.

Решение FAST

виновник, который замедляет вас, pg_timezone_names. После некоторого тестирования я обнаружил, что pg_timezone_abbrevs намного превосходит. Разумеется, для достижения этой цели вам нужно сохранить правильные сокращения сокращений часовых поясов вместо имен часовых поясов. Названия часовых поясов учитывают DST автоматически, аббревиатуры часовых поясов - это в основном просто коды для временного смещения. Документация:

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

Посмотрите на эти результаты тестов или попробуйте сами:

SELECT * FROM  pg_timezone_names;

Общее время выполнения: 541,007 ms

SELECT * FROM pg_timezone_abbrevs;

Общая продолжительность выполнения: 0,523 ms

Фактор 1000. Если вы идете с идеей бросить на text и вернуться к timetz или с моим методом для вычисления времени, это не важно. Оба метода очень быстры. Просто не используйте pg_timezone_names.

Собственно, как только вы сэкономите сокращения часового пояса, вы можете взять маршрут кастинга без дополнительных дополнительных. Используйте аббревиатуру вместо utc_offset. Результаты являются точными в соответствии с вашим определением.

CREATE TEMP TABLE schedule(t time, abbrev text);
INSERT INTO schedule values
 ('12:00:00', 'PST')  -- 'America/Vancouver'
,('12:00:00', 'MST')  -- 'US/Mountain'
,('12:00:00', 'CST'); -- 'America/Regina'

-- calculating
SELECT s.t AT TIME ZONE s.abbrev
     - a.utc_offset
     + EXTRACT (timezone from now()) * interval '1s'
FROM   schedule s
JOIN   pg_timezone_abbrevs a USING (abbrev);

-- casting (even faster!)
SELECT (t::text || abbrev)::timetz
FROM   schedule s;