[postgresql] PostgreSQL에서 시간대가 있거나없는 타임 스탬프의 차이점

Program / 글쓴이

데이터 유형이 WITH TIME ZONEvs 일 때 타임 스탬프 값이 PostgreSQL에 다르게 저장 WITHOUT TIME ZONE됩니까? 간단한 테스트 사례로 차이점을 설명 할 수 있습니까?

답변

차이점은 날짜 / 시간 유형에 대한 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)

답변

참조 된 PostgreSQL 설명서보다 이해하기 쉽게 설명하려고합니다.

TIMESTAMP변형은 이름에서 제안하는 내용에도 불구하고 시간대 (또는 오프셋)를 저장 하지 않습니다 . 차이점은 저장 형식 자체가 아니라 저장된 데이터 (및 의도 된 응용 프로그램)의 해석에 있습니다.

  • TIMESTAMP WITHOUT TIME ZONE저장 현지 날짜 – 시간 (일명. 벽에 달력의 날짜와 벽 시계 시간). 표준 시간대는 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 ZONEUTC 타임 라인에 포인트를 저장합니다. 어떻게 보이는지 (시간, 분 등)는 시간대에 따라 다르지만 항상 실제 “실제 사건의 순간”과 같은 “물리적”순간을 나타냅니다. 입력은 내부적으로 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 ZONEUTC 시간대로 렌더링되어 현지 날짜-시간과 동일한 연도, 월, 일,시, 분, 초 및 마이크로 초를 제공하는으로 저장됩니다. 그러나 UTC 해석에서 말하는 타임 라인의 요점을 나타내는 것은 아니며 현지 날짜-시간 필드가 인코딩되는 방식입니다. (실시간 시간대는 UTC가 아니기 때문에 타임 라인의 일부 점입니다. 우리는 그것이 무엇인지 알 수 없습니다.)

답변

여기 도움이 될만한 예가 있습니다. 시간대가있는 타임 스탬프가있는 경우 해당 타임 스탬프를 다른 시간대로 변환 할 수 있습니다. 기본 시간대가 없으면 올바르게 변환되지 않습니다.

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

답변