[postgresql] timezone의 모든 것?

출처 : blog.billo.io/devposts/psql_at_time_zone/

 

회사에서 PSQL 쿼리문을 작성하던 동료분이 이상한 부분이 있다며 불렀다. AT TIME ZONE 'Asia/Seoul' 을 사용했을 때와 AT TIME ZONE 'UTC+9' 를 사용했을 때 결과가 18시간이나 차이난다고 했다. 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)

------------------

[PostgreSQL] 시간값 다루기

시간 타입

postgresql의 시간 관리용 타입으로는 timestamp, date, time 등이 있다.

timestamp는 날짜와 시간값을 저장하는 8바이트 값이다. 표현범위는 BC.4713~AD.294276년이다. 넉넉하다.

date는 날짜정보만을 저장하는 4바이트 값이다. 표현범위는 BC.4713~AD.5874897년이다.

time은 시간정보만 저장하는 8바이트 값이다.

​

​

시간값 획득하기

시간값 획득에는 몇가지 방법이 있다.

첫번째는 NOW 함수를 사용하는 것이다.

 

 

이건 타임스탬프에 타임존이 합쳐진 값을 반환한다.

​

두번째 방법은 CURRENT_TIMESTAMP라는 특수한 값을 사용하는 것이다.

 

 

NOW 함수를 호출하는 것과 동등하다.

​

이외에도 현재 시간을 가져오는 CURRENT_TIME과

 

 

​

현재 날짜를 가져오는 CURRENT_DATE 등이 있다.

 

 

​

​

​

타임스탬프<->문자열 간 변환

TO_CHAR 함수를 사용하면 된다.

첫번째 인자로 타임스탬프, 두번째 인자로 형식 문자열을 받는다.

 

 

​

저기서 Y는 연도, M은 월, D는 일이다. 이외에도 많다.

 

 

​

그 반대는 TO_TIMESTAMP 함수로 수행해낼 수 있다.

 

 

​

​

타임스탬프<->Date 간 변환

타임스탬프->Date 변환은 단순히 캐스팅으로도 해결할 수 있다,

 

 

​

반대도 마찬가지다.

 

 

​

​

타임스탬프<->Time 간 변환

이것도 마찬가지로 캐스팅만으로 해결이 가능하다.

 

 

​

​

유닉스 타임스탬프

유닉스 타임스탬프는 1970년~2038년 범위만 표현할 수 있는 얄팍하고 구시대적인 시간 형식이다. Epoch나 posix time으로도 부른다.

4바이트 정수로 표현되며, 최소 단위는 1초다.

​

유닉스 타임스탬프는 바로 획득할 방법은 없고, 타임스탬프에 특수한 함수를 사용해 추출해야 한다.

획득은 다음과 같이 할 수 있다.

 

 

​

근데 밀리초단위가 소수점으로 나오기 때문에, 정수 캐스팅도 해주는 게 좋다.

 

 

​

유닉스 타임스탬프를 다시 타임스탬프로 바꿀 때는, 그냥 TO_TIMESTAMP만 사용해도 된다.

 

 

​

​

시간 값 변경: INTERVAL

시간을 다루다보면 시간값에 다른 시간을 더하거나 빼서 범위를 체크하는 경우가 무수히 많다.

당연히도 postgres는 이를 위한 기능을 제공하는데, 그게 바로 INTERVAL 타입이다.

이건 날짜 계산만을 위한 특수한 타입이다.

INTERVAL이 앞에 붙는 문자열로 표현된다.

 

 

저기엔 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 등이 들어갈 수 있고

​

자유롭게 시간값에 대해 연산을 수행할 수 있다.

 

 

​

​

시간 간격 계산

보통 타임스탬프끼리 뺄셈을 해서 지난 날짜를 가져오려 하면 INTERVAL 값으로 반환을 한다.

 

 

​

AGE 함수를 사용하면 DATE 값이 반환된다.

 

 

​

​

시간 부분값 추출: EXTRACT

extract는 타임스탬프 값에서 특정 부분을 추출해서 반환한다.

일이라든지, 연도라든지...

 

 

​

​

시간값 내림: DATE_TRUNC

DATE_TRUNC는 타임스탬프 값을 받아서, 특정 단위 밑을 잘라버리는 함수다.

가령 minute을 받는다면, 초 값이 00으로 잘려나간다.

 

 

​

​

타임존

지금 내 환경은 한국이라 상관없는데, DB 환경에 따라 각각 다른 타임존을 갖고있을 수 있다.

내 경우엔 서울로 잘 되어있지만

 

 

​

리눅스인 경우엔 대부분 UTC다.

 

 

그리고 모든 배포 서버는 리눅스다.

​

그럼 분명 지금 한국시간은 오늘인데, NOW()로 보면 어제가 나오는 일이 벌어질 수 있다.

 

 

이 부조화를 보아라

DB 내에서만 잘 사용한다면 상관없겠지만, 사용자에게 뿌려주는 일이 생기면 문제가 생긴다.

​

그럴 때는 타임존을 명시적으로 지정하면 한국시간을 가져올 수 있다.

 

 

​

이외에도 [PostgreSQL] 시간값 다루기