Часовой пояс с переходом на летнее время в PostgreSQL

Мы развертываем наши собственные датчики потока (очень похожие на этот датчик USGS: http://waterdata.usgs.gov/usa/nwis/uv?site_no=03539600), чтобы мы, каякеры, знали, достаточно ли воды, чтобы плыть по ручью, и не тратить время и бензин на поездку. Мы надеемся установить несколько из них в юго-восточном регионе Уайтуотер, который охватывает восточный и центральный часовые пояса.

Я сохраняю время вставки записи, используя значение по умолчанию current_time для записи. Я хотел бы позже отображать данные в формате MM/DD/YYYY HH12:MI AM TZ, который выводит как 03/12/2012 01:00 AM CDT. Я также хотел бы, чтобы вывод был в курсе изменений в дневном времени, чтобы последняя часть предыдущего предложения менялась между CST и CDT, когда мы «прыгаем вперед» и «отступаем». Это изменение произошло 3/11/2012 в этом году, и я включил даты по обе стороны от этой строки летнего времени ниже. Я использую свой ноутбук с Windows 7 для разработки, и позже мы будем развертывать его на платформе Unix. Постгрес, по-видимому, обнаружил, что на моем компьютере с Windows установлен часовой пояс восточной части США. Я пытаюсь сделать это с полем «отметка времени без часового пояса» и «отметка времени с полем часового пояса», но не могу заставить его работать.

Я пробовал использовать в своем выборе «часовой пояс», и все работает, пока не пришло время отображать часовой пояс. Фактический час является частью метки времени и правильно вычитается на час, когда я запрашиваю время в CDT. Но на выходе отображается EDT.

SELECT reading_time as raw,
       reading_time at time zone 'CDT',
       to_char(reading_time at time zone 'CDT',
           'MM/DD/YYYY HH12:MI AM TZ') as formatted_time
  FROM readings2;

"2012-04-29 17:59:35.65";"2012-04-29 18:59:35.65-04";"04/29/2012 06:59 PM EDT"
"2012-04-29 17:59:40.19";"2012-04-29 18:59:40.19-04";"04/29/2012 06:59 PM EDT"
"2012-03-10 00:00:00";"2012-03-10 00:00:00-05";"03/10/2012 12:00 AM EST"
"2012-03-11 00:00:00";"2012-03-11 00:00:00-05";"03/11/2012 12:00 AM EST"
"2012-03-12 00:00:00";"2012-03-12 01:00:00-04";"03/12/2012 01:00 AM EDT"

Я сохраняю часовой пояс, в котором каждый из наших датчиков расположен в поле с изменяющимся символом в отдельной таблице. Я подумал о том, чтобы просто добавить это значение в конец вывода времени, но я хочу, чтобы оно изменилось с CST на CDT без моего вмешательства.

Спасибо за вашу помощь.


postgresql timestamp timezone
person Debaser    schedule 30.04.2012    source источник
comment
Было бы полезно, если бы вы могли разместить здесь вывод psql's \d readings2.   -  person vyegorov    schedule 30.04.2012

Ответы (3)


arrow_upward
45
arrow_downward

Вместо использования имен часовых поясов, таких как CDT или CST, вы можете рассмотреть возможность использования полных имен часовых поясов в стиле Ольсена. В случае центрального времени вы можете выбрать часовой пояс. Либо тот, который соответствует вашему местоположению, например America/Chicago, либо просто US/Central. Это гарантирует, что PostgreSQL использует базу данных Olsen tz для автоматического определения того, применяется ли летнее время в любой заданный день.

person Guan Yang    schedule 30.04.2012

arrow_upward
7
arrow_downward

Вам определенно нужен столбец TIMESTAMP WITH TIME ZONE (который также известен как timestamptz в PostgreSQL). Это сохранит метку времени в формате UTC, так что она будет представлять конкретный момент времени. Вопреки тому, что следует из названия, он не сохраняет часовой пояс в столбце - вы можете просмотреть полученную метку времени в выбранном вами часовом поясе с помощью фразы AT TIME ZONE.

Семантика TIMESTAMP WITHOUT TIME ZONE сбивает с толку и почти бесполезна. Я настоятельно рекомендую вам вообще не использовать этот тип для того, что вы описываете.

Меня действительно смущает часть вопроса, в которой говорится о хранении метки времени в столбце CHARACTER VARYING. Похоже, это может быть частью проблемы. Если вы можете сохранить его в timestamptz с самого начала, я подозреваю, что у вас будет меньше проблем. За исключением этого, было бы безопаснее использовать нотацию -04 для смещения от UTC; но мне кажется, что это больше бесполезно.

person kgrittn    schedule 30.04.2012
comment
Чтобы добавить свой ответ, вот ссылка, посвященная этому более глубокому вопросу: phili .pe / posts / timestamps-and-time-zone-in-postgresql. - person jgburet; 23.03.2017

arrow_upward
1
arrow_downward

Вы можете создать таблицу известных часовых поясов в формате, предложенном в ответе Гуань Янга, а затем использовать столбец внешнего ключа для этого стол. Действительные часовые пояса можно узнать из pg_timezone_names I более подробно рассказано в этом соответствующем ответе.

person beldaz    schedule 07.03.2015