Разница между отметками времени с / без часового пояса в PostgreSQL

значения временных меток хранятся по-разному в PostgreSQL, когда тип данных WITH TIME ZONE и WITHOUT TIME ZONE? Можно ли проиллюстрировать эти различия простыми тестовыми примерами?

868   4  

4 ответов:

различия рассматриваются в документация PostgreSQL для типов даты/времени. Да, лечение TIME или TIMESTAMP отличается между WITH TIME ZONE или WITHOUT TIME ZONE. Это не влияет на то, как хранятся значения; это влияет на то, как они интерпретируются.

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

  • с часовым поясом в качестве части значения, значение может быть отображено как местное время в клиенте.

  • без часового пояса в качестве части значения очевидным часовым поясом по умолчанию является UTC, поэтому он отображается для этого часового пояса.

поведение зависит от трех факторов:

  • настройка часового пояса в клиенте.
  • тип данных (т. е. WITH TIME ZONE или WITHOUT TIME ZONE) значения.
  • указано ли значение с определенным часовым поясом.

вот примеры, охватывающие комбинации этих факторов:

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)
98  
2018-01-05 00:42:58

я пытаюсь объяснить это более понятно, чем упомянутая документация PostgreSQL.

ни TIMESTAMP варианты хранят часовой пояс (или смещение), несмотря на то, что предлагают имена. Разница заключается в интерпретации сохраненных данных (и в предполагаемом приложении), а не в самом формате хранения:

  • TIMESTAMP WITHOUT TIME ZONE магазинах local дата-время (ака. настенный календарь дата и настенные часы время). Часовой пояс не указан насколько PostgreSQL может сказать (хотя ваше приложение может знать, что это такое). Следовательно, PostgreSQL не выполняет преобразование, связанное с часовым поясом, на входе или выходе. Если значение было введено в базу данных как '2011-07-01 06:30:30', то независимо от того, в каком часовом поясе вы отображаете его позже, он все равно будет говорить Год 2011, месяц 07, день 01, 06 часов, 30 минут и 30 секунд (в некотором формате). Кроме того, любое смещение или часовой пояс, указанный во входных данных, игнорируется PostgreSQL, поэтому '2011-07-01 06:30:30+00' и '2011-07-01 06:30:30+05' такие же, как и просто '2011-07-01 06:30:30'. Для разработчиков Java: это аналогично java.time.LocalDateTime.

  • TIMESTAMP WITH TIME ZONE сохраняет точку на линии времени UTC. Как он выглядит (сколько часов, минут и т. д.) зависит от вашего часового пояса, но он всегда относится к одному и тому же "физическому" моменту (например, момент фактического физического события). Этот ввод внутренне преобразуется в UTC, и именно так он хранится. Для этого необходимо знать смещение входа, поэтому, когда вход не содержит явного смещения или часового пояса (например '2011-07-01 06:30:30') предполагается, что он находится в текущем часовом поясе сеанса PostgreSQL, в противном случае используется явно указанное смещение или часовой пояс (как в '2011-07-01 06:30:30+05'). Вывод отображается преобразованным в текущий часовой пояс сеанса PostgreSQL. Для разработчиков Java: это аналогично java.time.Instant (С более низким разрешением, хотя), но с JDBC и JPA 2.2 вы должны сопоставить его с java.time.OffsetDateTime (или java.util.Date или java.sql.Timestamp конечно).

некоторые говорят, что обе TIMESTAMP вариации хранить UTC дата-время. Отчасти, но это сбивает с толку, чтобы выразить это таким образом, на мой взгляд. TIMESTAMP WITHOUT TIME ZONE хранение TIMESTAMP WITH TIME ZONE, который отображается с часовым поясом UTC, дает тот же год, месяц, день, часы, минуты, секунды и микросекунды, что и в локальном времени даты. Но он не предназначен для представления точки на временной линии, о которой говорит интерпретация UTC, это просто способ кодирования локальных полей даты и времени. (Это какой-то кластер точек на временной линии, как настоящий часовой пояс не UTC; мы не знаем, что это такое.)

10  
2018-04-26 17:43:40

вот пример, который должен помочь. Если у вас есть метка времени с часовым поясом, вы можете преобразовать эту метку времени в любой другой часовой пояс. Если у вас нет базового часового пояса, он не будет преобразован правильно.

SELECT now(),
   now()::timestamp,
   now() AT TIME ZONE 'CST',
   now()::timestamp AT TIME ZONE 'CST'

выход:

-[ RECORD 1 ]---------------------------
now      | 2018-09-15 17:01:36.399357+03
now      | 2018-09-15 17:01:36.399357
timezone | 2018-09-15 08:01:36.399357
timezone | 2018-09-16 02:01:36.399357+03
9  
2018-09-15 17:04:15 
SELECT MAX('2017-07-06 12:20:48.446+00') - MIN('2017-06-06 12:20:48.446+00') 
as time_to_take  from table_name
-1  
2017-06-13 11:18:19

Comments

    Ничего не найдено.
Click here to cancel reply.