PostgreSQL date() с часовым поясом

Ответ 1

Я не вижу точный тип starts_at в вашем вопросе. Вы действительно должны включить эту информацию, это ключ к решению. Я должен угадать.

По сути, PostgreSQL всегда хранит значение времени UTC для timestamp with time zone типа timestamp with time zone внутри. Только отображение зависит от вашей текущей настройки timezone. Эффект конструкции AT TIME ZONE также меняется в зависимости от базового типа данных. Больше деталей:

• Игнорирование часовых поясов в Rails и PostgreSQL

Если вы извлекаете date из типа timestamp [without time zone], вы получите дату для текущего часового пояса. День на выходе будет таким же, как на дисплее значения timestamp.

Если вы извлекаете date из типа timestamp with time zone (timestamptz для краткости), смещение часового пояса "применяется" первым. Вы по-прежнему получаете дату для текущего часового пояса, которая согласуется с отображением отметки времени. Тот же момент времени переводится на следующий день в некоторых частях Европы, например, в Калифорнии за 16 часов. Чтобы получить дату для определенного часового пояса, сначала примените AT TIME ZONE.

Поэтому то, что вы описываете в верхней части вопроса, противоречит вашему примеру.

Учитывая, что starts_at является timestamp [without time zone] а время на вашем сервере установлено на местное время. Тест с:

SELECT now();

Отображает ли оно то же время, что и часы на вашей стене? Если да (и сервер БД работает с правильным временем), настройка timezone вашего текущего сеанса согласуется с вашим часовым поясом. Если нет, вы можете перейти к настройке timezone в вашем postgresql.conf или в вашем клиенте для сеанса. Подробности в руководстве.

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

• Обработка часового пояса в базе данных Postgres

Чтобы получить вашу локальную дату от starts_at просто

SELECT starts_at::date

Точно так же:

SELECT date(starts_at)

Кстати, ваше местное время сейчас на UTC-7, а не на UTC-8, потому что действует летнее время (не среди самых ярких представлений человечества).

Стандартное тихоокеанское время (PST) обычно на 8 часов "раньше" (большее значение timestamp), чем UTC (универсальный часовой пояс), но в периоды летнего времени (как сейчас) оно может составлять 7 часов. Вот почему timestamptz отображается как 2012-06-21 02:00:00 -07 в вашем примере. Конструкция AT TIME ZONE 'PST' учитывает переход на летнее время. Эти два выражения дают разные результаты (одно зимой, другое летом) и могут приводить к разным датам при приведении:

SELECT '2012-06-21 01:00:00'::timestamp AT TIME ZONE 'PST'
     , '2012-12-21 01:00:00'::timestamp AT TIME ZONE 'PST'

Ответ 2

В основном вы хотите:

$ select starts_at AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific' from schedules where id = 40

Я получил решение из этой статьи ниже, которое прямо GOLD!!! Он объясняет эту нетривиальную проблему очень четко, дайте ей прочитать, если вы хотите лучше понять управление pstgrsql TZ.

Выражение временных меток PostgreSQL без зон в локальное время

Вот что происходит. Сначала вы должны знать, что "часовой пояс PST на 8 часов отстает от часовой пояс UTC, так что, например, 1 января 2014 года, 16:30 по тихоокеанскому времени (ср., 01 января 2014 года 16:00:30 -0800) эквивалентно 2 января 2014 года, 00:30 AM UTC (Чт, 02 января 2014 00:00:30 +0000). Каждый раз после 16:00 в PST проскальзывает до следующего дня, интерпретируется как UTC.

Кроме того, как упоминал выше Эрвин Брандштетер, postresql имеет два типа данных временных меток: один с часовым поясом и один без него. Если ваши временные метки включают часовой пояс, то простой:

$ select starts_at AT TIME ZONE 'US/Pacific' from schedules where id = 40

будет работать. Однако, если ваша временная метка не указана во времени, выполнение указанной выше команды не будет работать, и вы должны ПЕРВОЕ преобразовать вашу временную метку времени в временную метку с часовым поясом, а именно часовой пояс UTC, и ТОЛЬКО ТОГДА преобразуйте его в желаемый "PST" или "US/Тихоокеанский регион" (которые являются одними и теми же вопросами, не относящимися к летнему времени). Я думаю, что с вами тоже должно быть хорошо.

Позвольте мне продемонстрировать пример, где я создаю временную метку времени. Предположим для удобства, что наш локальный часовой пояс действительно является "PST" (если бы он не был тогда, он немного сложнее, что не нужно для целей этого объяснения).

Скажем, у меня есть:

$ select timestamp '2014-01-2 00:30:00' AS a, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AS b,  timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AT TIME ZONE 'PST' AS c, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

Это даст:

"a"=>"2014-01-02 00:30:00"   (This is the timezoneless timestamp)
"b"=>"2014-01-02 00:30:00+00" (This is the UTC TZ timestamp, note that up to a timezone, it is equivalent to the timezoneless one)
"c"=>"2014-01-01 16:30:00" (This is the correct 'PST' TZ conversion of the UTC timezone, if you read the documentation postgresql will not print the actual TZ for this conversion)
"d"=>"2014-01-02 08:30:00+00"

Последняя временная метка является причиной всякой путаницы в отношении преобразования timezoneless timestamp из UTC в "PST" в postgresql. Когда мы пишем:

timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

Мы используем временную метку времени и пытаемся преобразовать ее в PST TZ (мы косвенно предполагаем, что postgresql поймет, что мы хотим, чтобы он преобразовал временную метку из UTC TZ, но postresql имеет собственные планы!). На практике, что postgresql делает, это занимает timezoneless timestamp ('2014-01-2 00:30:00) и относится к нему, как если бы он был УЖЕ "PST' TZ timestamp (то есть: 2014-01-2 00:30: 00 -0800) и преобразует это в часовой пояс UTC!!! Таким образом, это на самом деле толкает его на 8 часов вперед, а не обратно! Таким образом, мы получаем (2014-01-02 08: 30: 00 + 00).

В любом случае, это последнее (неинтуитивное) поведение является причиной всей путаницы. Прочитайте статью, если вы хотите более подробное объяснение, я действительно получил результаты, которые немного отличаются от их последней части, но общая идея такая же.

Ответ 3

Я знаю, что это старый, но вы можете захотеть использовать AT TIME ZONE "US/Pacific" при кастинге, чтобы избежать проблем с PST/PDT. Итак,

SELECT starts_at:: TIMESTAMPTZ НА ВРЕМЕННОЙ ЗОНЕ "US/Pacific" FROM schedules WHERE ID = '40';

Ответ 4

cast(master.Stamp5DateTime as date) >= '05-05-2019' AND 

cast(master.Stamp5DateTime as date) <= '05-05-2019'