PSQL TIMESTAMP/AT TIME ZONE 바로 알기
회사에서 PSQL 쿼리문을 작성하던 동료분이 이상한 부분이 있다며 불렀다. AT TIME ZONE 'Asia/Seoul' 을 사용했을 때와 AT TIME ZONE 'UTC+9' 를 사용했을 때 결과가 18시간이나 차이난다고 했다.
postgres=# SHOW timezone;
TimeZone
------------
Asia/Seoul
(1 row)
postgres=# SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'Asia/Seoul';
timezone
---------------------
2020-08-06 12:00:00+09
(1 row)
postgres=# SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'UTC+9';
timezone
------------------------
2020-08-07 06:00:00+09
(1 row)
음.. 상식대로라면 둘의 결과가 같아야 할 것 같은데, 왜 다른건지, 뭐가 맞는건지 궁금해졌다. 구글링해봐도 별다른 답을 얻을 수는 없었다. 그리고 찾아보게 된 PSQL의 timezone table.
postgres=# SELECT * FROM pg_timezone_names WHERE abbrev='KST';
name | abbrev | utc_offset | is_dst
----------------+--------+------------+--------
Asia/Seoul | KST | 09:00:00 | f
Asia/Pyongyang | KST | 09:00:00 | f
ROK | KST | 09:00:00 | f
(3 rows)
Asia/Seoul은 9시간으로 정상적으로 등록 되어있었다. 혼란스러웠다. 그래서 이번에 문제의 원인을 찾는 김에 대충 알고 쓰고있었던 timestamp 관련 내용을 좀 찾아보기로 했다.
우선, Postgres에서 시간을 저장하는 두가지 타입에 대해서 찾아보았다. 바로 TIMESTAMP [WITHOUT TIME ZONE] 와 TIMESTAMP WITH TIME ZONE 이다.
TIMESTAMP 타입은 TIMESTAMP '2020-08-06 12:00:00' 형태처럼 timezone 값을 따로 명시하지 않고 사용하는 값을 말한다. TIMESTAMP WITHOUT TIME ZONE은 TIMESTAMP 로 줄여서 사용할 수 있다.
TIMESTAMP WITH TIME ZONE 타입은 TIMESTAMP WITH TIME ZONE '2020-08-06 12:00:00+10' 형태처럼 뒤에 어느 타임존에서의 timestamp인지를 나타낸 timestamp 값을 이야기한다. 이때, TIMESTAMP WITH TIME ZONE이라도 저장할 때 time zone 정보를 같이 저장하지 않는다. 값은 UTC 기준 timestamp로 변환되어 저장되고, 출력할 일이 있을 때 시스템의 time zone으로 출력하게 된다. (SHOW TIMEZONE 커멘드로 PSQL에서 사용하는 시스템의 time zone을 알 수 있다. )
예를 들어보자. SELECT TIMESTAMP WITH TIME ZONE '2020-08-06 12:00:00+10'; 을 Asia/Seoul에서 실행하게 되면 어떻게 될까?
postgres=# SELECT TIMESTAMP WITH TIME ZONE '2020-08-06 12:00:00+10';
timestamptz
------------------------
2020-08-06 11:00:00+09
(1 row)
Asia/Seoul time zone 형태로 표시된 해당 시간을 얻을 수 있다.
우리가 알고싶었던 AT TIME ZONE 은 사실 3가지 역할을 하는데, (문서)
TIMESTAMP WITHOUT TIME ZONE AT TIME ZONE zone형태로, timestamp without time zone을 해당zone에서의 timestamp라고 생각하고, timestamp with time zone 값을 반환한다.TIMESTAMP WITH TIME ZONE AT TIME ZONE zone형태로, timestamp with time zone 값을 해당zone에서의 timestamp without time zone 값을 반환한다.- 여기서는 다루지 않는 값으로 생략한다.
하나씩 보자.
1번은 zone에서 TIMESTAMP WITHOUT TIME ZONE 인 시각을 결과값으로 전달해준다는 이야기이다. 예시로, SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'Australia/Melbourne'; 을 실행하면 어떻게 될까? 시스템 timezone은 Asia/Seoul 이고, Australia/Melbourne은 UTC+10이다.
멜버른에서 2020-08-06 12:00:00 인 시각에 해당하는 TIMESTAMP WITH TIME ZONE 을 보여줄텐데, 이 때 TIMESTAMP WITH TIME ZONE은 시스템 timezone에 맞춰서 보여주므로,
postgres=# SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'Australia/Melbourne';
timezone
------------------------
2020-08-06 11:00:00+09
(1 row)
위 처럼 보이게 된다.
2번은 TIMESTAMP WITH TIME ZONE 을 AT TIME ZONE zone하면, 해당 시각일 때, zone 에서는 현재 어떤 시간을 표시하고 있을까? 를 반환한다는 이야기이다. 이 때는 TIMESTAMP WITHOUT TIME ZONE을 반환하게 된다. 예를 들어 SELECT TIMESTAMP WITH TIME ZONE '2020-08-06 12:00:00+10' AT TIME ZONE 'Asia/Seoul'; 을 한다면 어떻게 될까?
postgres=# SELECT TIMESTAMP WITH TIME ZONE '2020-08-06 12:00:00+10' AT TIME ZONE 'Asia/Seoul';
timezone
---------------------
2020-08-06 11:00:00
(1 row)
멜버른에서 정오일때 서울은 11시이므로, 위처럼 나오게 된다.
위의 배경지식을 갖고 볼 때, 기존에 궁금했던 SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'Asia/Seoul'; 와 SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'UTC+9'; 의 결과 중에서는 첫번째 결과가 맞다는 것을 알 수 있었다. 그 다음 의심해 봐야 하는 것은 TIME ZONE을 어떻게 표기하느냐에 관한 것이다.
PSQL에서는 timezone을 3가지 방법으로 표시할 수 있는데, (문서)
Asia/Seoul형태의 Timezone full namePST형태의 줄임말- POSIX-Style의 Timezone 표기법인
STDoffset,STDoffsetDST(DST는 일광절약시간) 형태. e.g.KST5,EDT5DST
문제는 3번에 있었다. 우리가 UTC+9 형태로 표시하면 3번에 해당하게 되는데, 이 POSIX-Style에서 offset을 일반적으로 세는 방향과 반대인 numeric offset in hours west from UTC 즉 UTC로부터 서쪽으로 시간상 얼마나 떨어져 있는지를 세고 있던 것이다. 우리가 알고있는 UTC+9의 형태는 동쪽으로 계산한 값이다. 따라서 AT TIME ZONE 'UTC+9' 는 우리나라랑은 그리니치 천문대 기준으로 대칭적인 위치의 시간대에 해당하는 값이어서, 값이 이상하게 나왔던 것이다.
간단해 보였던 질문을 해결하기 위해 관련된 개념들을 정독하게 되었는데, 이해도가 올라가서 기분이 좋다. 특히 이 질문을 해결하기 위해서 구글링을 참 많이 했는데, 적절한 질문을 찾기가 힘들었다. 이럴 때는 역시 Documentation을 읽어야 한다는걸 느꼈다. Documentation을 읽으니 대충 알던 개념도 잘 정리되고, internal에 대한 정보도 얻을 수 있었다.