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

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

---

데이터 유형이 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 stores local date-time (aka. wall calendar date and wall clock time). Its time zone is unspecified as far as PostgreSQL can tell (though your application may knows what it is). Hence, PostgreSQL does no time zone related conversion on input or output. If the value was entered into the database as '2011-07-01 06:30:30', then no mater in what time zone you display it later, it will still say year 2011, month 07, day 01, 06 hours, 30 minutes, and 30 seconds (in some format). Also, any offset or time zone you specify in the input is ignored by PostgreSQL, so '2011-07-01 06:30:30+00' and '2011-07-01 06:30:30+05' are the same as just '2011-07-01 06:30:30'. For Java developers: it's analogous to java.time.LocalDateTime.

• TIMESTAMP WITH TIME ZONE stores a point on the UTC time line. How it looks (how many hours, minutes, etc.) depends on your time zone, but it always refers to the same "physical" instant (like the moment of an actual physical event). The input is internally converted to UTC, and that's how it's stored. For that, the offset of the input must be known, so when the input contains no explicit offset or time zone (like '2011-07-01 06:30:30') it's assumed to be in the current time zone of the PostgreSQL session, otherwise the explicitly specified offset or time zone is used (as in '2011-07-01 06:30:30+05'). The output is displayed converted to the current time zone of the PostgreSQL session. For Java developers: It's analogous to java.time.Instant (with lower resolution though), but with JDBC and JPA 2.2 you are supposed to map it to java.time.OffsetDateTime (or to java.util.Date or java.sql.Timestamp of course).

Some say that both TIMESTAMP variations store UTC date-time. Kind of, but it's confusing to put it that way in my opinion. TIMESTAMP WITHOUT TIME ZONE is stored like a TIMESTAMP WITH TIME ZONE, which rendered with UTC time zone happens to give the same year, month, day, hours, minutes, seconds, and microseconds as they are in the local date-time. But it's not meant to represent the point on the time line that the UTC interpretation says, it's just the way the local date-time fields are encoded. (It's some cluster of dots on the time line, as the real time zone is not UTC; we don't know what it is.)

---

Here is an example that should help. If you have a timestamp with a timezone, you can convert that timestamp into any other timezone. If you haven't got a base timezone it won't be converted correctly.

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

Output:

-[ 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

참고URL : https://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql