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

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

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

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

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

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

Следовательно, то, что вы описываете в начале вопроса, противоречит вашему примеру.

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

SELECT now();

Отображает ли он то же время, что и часы на стене? Если да (и сервер db работает с правильным временем), 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'

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

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

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

Выражение временных меток PostgreSQL без зон по местному времени

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

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

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

буду работать. Однако, если ваша метка времени без часового пояса, выполнение указанной выше команды не будет работать, и вы должны СНАЧАЛА преобразовать метку времени без часового пояса в метку времени с часовым поясом, а именно часовой пояс UTC, и ТОЛЬКО ЗАТЕМ преобразовать ее в желаемый PST или US / Pacific »(которые одинаковы до некоторых проблем с переходом на летнее время. Я думаю, вам все будет хорошо).

Позвольте мне продемонстрировать пример, в котором я создаю временную метку без часового пояса. Предположим для удобства, что наш местный часовой пояс действительно является «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"

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

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

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

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

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

SELECT starts_at::TIMESTAMPTZ AT TIME ZONE "US/Pacific" 
  FROM schedules 
 WHERE ID = '40';