[Postgresql] timestamp vs timestamptz 비교 & 테스트

pg의 데이터 타입인 timestamp 와 timestamptz의 차이점에 대해 알아보자

이 문서는 postgres 12 기준으로 작성되었다.

postgresql 문서

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

=> timestamptz : timestamp with time zone의 약어

• timestamp : 타임존을 명시하지 않는 날짜와 시간

ex) 2004-10-19 10:23:54

• timestamptz(timestamp with time zone) : 타임존을 명시한 날짜와 시간
  • 저장할 때는 UTC0 기준 timestamp로 변환되어 저장되고, 출력할 일이 있을 때 디폴트는 pg의 time zone 혹은 설정한 존으로 변환해 출력하게 된다.

ex) 2004-10-19 10:23:54+02

테스트

test0. 조건

1. 서울(+09) / 로스엔젤레스(-08)

• 17시간 차이(로스엔젤레스 = 서울 - 17)

타임존 확인

postgres=# show timezone;
  TimeZone  
------------
 Asia/Seoul
(1 row)

now() 확인

postgres=# select now();
              now              
-------------------------------
 2022-02-08 14:37:50.170236+09
(1 row)

테스트 테이블 생성

CREATE TABLE public.test_timestamp (
  timestamp_without_timezone timestamp,
  timestamp_with_timezone timestamptz
);

테스트 데이터 insert

insert into test_timestamp  values(now(), now());

test1. pg의 타임존 변경

테스트 데이터 확인

• timestamp : 타임존 정보 x
• timestamptz : 타임존 정보 o

postgres=# insert into test_timestamp  values(now(), now());
INSERT 0 1
postgres=# select * from test_timestamp;
 timestamp_without_timezone |    timestamp_with_timezone    
----------------------------+-------------------------------
 2022-02-08 14:38:38.562596 | 2022-02-08 14:38:38.562596+09
(1 row)

타임존 변경 후 확인

타임존 변경

postgres=# set timezone = 'America/Los_Angeles';
SET


postgres=# show timezone;
      TimeZone       
---------------------
 America/Los_Angeles
(1 row)


postgres=# select now();
              now              
-------------------------------
 2022-02-07 21:40:22.676379-08
(1 row

테스트 테이블 확인

• timestamp : timezone의 변경과 상관 없음
• timestamptz : timezone이 변경되면 timezone에 따라 자동으로 계산됨

postgres=# select * from test_timestamp;
 timestamp_without_timezone |    timestamp_with_timezone    
----------------------------+-------------------------------
 2022-02-08 14:38:38.562596 | 2022-02-07 21:38:38.562596-08
(1 row)

test2. 서버의 timezone 변경

도커가 올라가있는 서버의 timezone 변경

서버의 타임존 확인

[root@localhost ~]# timedatectl
      Local time: 화 2022-02-08 14:47:32 KST
  Universal time: 화 2022-02-08 05:47:32 UTC
        RTC time: 화 2022-02-08 05:47:32
       Time zone: Asia/Seoul (KST, +0900)  # 서울 +9
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: n/a

서버 타임존 변경

[root@localhost ~]# timedatectl set-timezone America/Los_Angeles


[root@localhost ~]# timedatectl
      Local time: 월 2022-02-07 21:51:05 PST
  Universal time: 화 2022-02-08 05:51:05 UTC
        RTC time: 화 2022-02-08 05:51:05
       Time zone: America/Los_Angeles (PST, -0800)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: no
 Last DST change: DST ended at
                  일 2021-11-07 01:59:59 PDT
                  일 2021-11-07 01:00:00 PST
 Next DST change: DST begins (the clock jumps one hour forward) at
                  일 2022-03-13 01:59:59 PST
                  일 2022-03-13 03:00:00 PDT

pg 확인

postgres=#  set timezone = 'America/Los_Angeles';
SET


postgres=# select now();
              now              
-------------------------------
 2022-02-07 22:14:12.756723-08
(1 row)


postgres=# insert into test_timestamp  values(now(), now());
INSERT 0 1


postgres=# select * from test_timestamp;
 timestamp_without_timezone |    timestamp_with_timezone    
----------------------------+-------------------------------
 2022-02-08 14:38:38.562596 | 2022-02-07 21:38:38.562596-08
 2022-02-07 22:14:49.0694   | 2022-02-07 22:14:49.0694-

원복

[root@localhost aiops]# timedatectl set-timezone Asia/Seoul


[root@localhost aiops]# timedatectl
      Local time: 화 2022-02-08 15:17:25 KST
  Universal time: 화 2022-02-08 06:17:25 UTC
        RTC time: 화 2022-02-08 06:17:25
       Time zone: Asia/Seoul (KST, +0900)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: n/a

docker 의 time zone 변경

컨테이너 접속

[root@localhost aiops]# docker exec -it 1cf87aee2deb /bin/bash


root@1cf87aee2deb:/# date
Tue 08 Feb 2022 03:19:40 PM KST

변경

root@1cf87aee2deb:/# dpkg-reconfigure tzdata
...
Current default time zone: 'America/Los_Angeles'
Local time is now:      Mon Feb  7 22:20:48 PST 2022.
Universal Time is now:  Tue Feb  8 06:20:48 UTC 2022.


root@1cf87aee2deb:/# date
Mon 07 Feb 2022 10:20:50 PM PST

pg 확인

postgres=# select now();
             now              
------------------------------
 2022-02-07 22:22:17.60773-08
(1 row)


postgres=# insert into test_timestamp  values(now(), now());
INSERT 0 1


postgres=# select * from test_timestamp;
 timestamp_without_timezone |    timestamp_with_timezone    
----------------------------+-------------------------------
 2022-02-08 14:38:38.562596 | 2022-02-07 21:38:38.562596-08
 2022-02-07 22:14:49.0694   | 2022-02-07 22:14:49.0694-08
 2022-02-07 22:22:54.473694 | 2022-02-07 22:22:54.473694-08
(3 rows

 

at time zone

1. timestamp without time zone AT TIME ZONE zone : 주어진 zone에서의 시간을 pg에 설정되어 있는 zone에서의 timestamptz로 반환
2. timestamp with time zone AT TIME ZONE zone : 주어진 값(timestamptz)에 있는 zone 에서의 시간을 zone에서의 timestamp 값을 반환

test0. 조건

1. 서울(+09) / 로스엔젤레스(-08)

• 17시간 차이(로스엔젤레스 = 서울 - 17)

2. pg timezone 서울로 설정

postgres=# set timezone = 'Asia/Seoul';
SET
postgres=# show timezone;
  TimeZone  
------------
 Asia/Seoul
(1 row)

test 1. timestamp without time zone AT TIME ZONE zone 테스트

• 주어진 zone에서의 시간을 pg에 설정되어 있는 zone에서의 timestamptz로 반환

## pg가 서울 기준이라 그대로 나옴
postgres=# select timestamp '2022-02-12 12:00:00' at time zone 'Asia/Seoul'; 
        timezone        
------------------------
 2022-02-12 12:00:00+09
(1 row)


## 로스엔젤레스 시간을 pg(서울)기준으로 보여줌
## pg가 서울 기준이기 때문에 +17 한 시간이 나옴(로스엔젤레스에서 12시 일 때 한국 시간)
postgres=# select timestamp '2022-02-12 12:00:00' at time zone 'America/Los_Angeles';
        timezone        
------------------------
 2022-02-13 05:00:00+09
(1 row)

test2. timestamp with time zone AT TIME ZONE zone 테스트

• 주어진 값(timestamptz)에 있는 zone 에서의 시간을 zone에서의 timestamp 값을 반환

## 서울은 그대로
postgres=# select timestamp with time zone '2022-02-12 12:00:00+09' at time zone 'Asia/Seoul';
      timezone       
---------------------
 2022-02-12 12:00:00
(1 row)


## 서울시간을 로스엔젤레스로 변환
postgres=# select timestamp with time zone '2022-02-12 12:00:00+09' at time zone 'America/Los_Angeles';
      timezone       
---------------------
 2022-02-11 19:00:00
(1 row)

zone 을 표시하는 3가지 방법

1. A full time zone name, for example America/New_York. The recognized time zone names are listed in the pg_timezone_names view (see Section 51.92). PostgreSQL uses the widely-used IANA time zone data for this purpose, so the same time zone names are also recognized by other software.

2. A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition rules as well. The recognized abbreviations are listed in the pg_timezone_abbrevs view (see Section 51.91). You cannot set the configuration parameters TimeZone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with the AT TIME ZONE operator.

3. In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications, as described in Section B.5. This option is not normally preferable to using a named time zone, but it may be necessary if no suitable IANA time zone entry is available.

1. Asia/Seoul형태의 Timezone full name
2. PST, KST형태의 줄임말
3. POSIX-Style의 Timezone 표기법인 STDoffset, STDoffsetDST (DST는 일광절약시간) 형태. e.g. KST5, EDT5DST

zone 표시 방법별 test

postgres=# select timestamp with time zone '2022-02-12 12:00:00+09' at time zone 'Asia/Seoul';
      timezone       
---------------------
 2022-02-12 12:00:00
(1 row)


postgres=# select timestamp with time zone '2022-02-12 12:00:00+09' at time zone 'KST';
      timezone       
---------------------
 2022-02-12 12:00:00
(1 row)


# 위의 결과와 18시간 차이
postgres=# select timestamp with time zone '2022-02-12 12:00:00+09' at time zone 'UTC+9';
      timezone       
---------------------
 2022-02-11 18:00:00
(1 row

-> POSIX-style UTC + : 서쪽으로 얼마나 떨어져 있는지 계산

(일반적으로 사용하는 UTC + : 동쪽으로 얼마나 떨어져 있는지 계산)

=> UTC+9는 일반적으로 말하는 UTC-9 이다. 그러므로 3번째 테스트에서 한국 시간 기준 12일 12시를 일반적으로 말하는 UTC-9로 출력하려면 -18 이므로 11일 18시가 나온다.

결론

1. timestamp : 타임존 변경에 따라 달라지는 점 없음
2. timestamptz : 타임존 변경에 따라 값이 변경됨
   1. 저장할 때는 UTC0 기준 timestamp로 변환되어 저장되고, 출력할 일이 있을 때 디폴트는 pg의 time zone 혹은 설정한 존으로 변환해 출력하게 된다.
3. 여기서 타임존은 pg의 타임존만 영향을 미침(서버의 타임존은 영향을 끼치지 않음)
4. timestamp without time zone AT TIME ZONE zone : 주어진 AT TIME zone에서의 시간을 pg에 설정되어 있는 zone에서의 timestamptz로 반환
   ex. select timestamp '2022-02-12 12:00:00' at time zone 'America/Los_Angeles';
   => la에서의 12시가 pg timezone인 서울에서는 몇 시인지 timestamptz로 출력
5. timestamp with time zone AT TIME ZONE zone : 주어진 값(timestamptz)에 있는 zone 에서의 시간을 AT TIME zone에서의 timestamp 값을 반환
   ex. select timestamp with time zone '2022-02-12 12:00:00+09' at time zone 'America/Los_Angeles';
   => 서울(주어진 timestamptz의 타임존은 +09 이므로 서울)에서의 12시가 la에서는 몇 시인지 timestamp로 출력
6. zone을 표시하는 방법은 3가지가 있는데 POSIX-style(ex. UTC+9)는 일반적으로 생각하는 UTC와 반대 방향(+ 가 서쪽) 이다. 일반적으로는 사용하지 않음.

참고

• https://blog.billo.io/devposts/psql_at_time_zone/
• https://www.postgresql.org/docs/12/index.html