[Project] 프로젝트 삽질기46 (feat Time Zone)

들어가며

도메인 별로 서버를 분리해서 프로젝트를 운영하고 있습니다. 특정 도메인 서버에서 created_at 데이터를 조회할 때 시간 데이터가 제대로 출력되지 않는 문제를 겪었습니다. 알고 보니, created_at 컬럼이 timestamp without time zone 타입이었기에 발생한 문제였는데요. PostgreSQL을 활용할 때 시간 데이터가 왜 제대로 출력되지 않았는지, timezone 설계를 어떻게 해야 하는지 파악해보고 싶었습니다. 이 글은 Postgres의 Time Zone에 대해 공부하며 작성됐습니다. 

 

 

 

 

 

 

---

 

 

timestamp vs timestampz

TypeORM을 활용하다 보면, CreateDateColumn 데코레이터를 활용하곤 하는데요. postgres를 활용하다 보면, type으로 timestampz와 timestamp를 설정해 줄 수 있습니다.

 

 

export abstract class BaseTimeEntity {
  @CreateDateColumn({ type: 'timestamptz', nullable: false })
  createdAt: Date;

  @CreateDateColumn({ type: 'timestampt', nullable: false })
  createdAt: Date;
}

 

 

timestamp와 timestampz를 설정했을 때 어떤 차이가 생길 수 있을까요? 이에 대해 알아보겠습니다.

 

 

Postgres Time Zone

Postgres에서 시간을 저장하는 타입에는 크게 두 가지 TIMESTAMP (Without Time Zone)와 TIMESTAMP (With Time Zone)가 있습니다.  

 

먼저 timestamp Without Time zone은 타임존을 명시하지 않는 날짜와 시간입니다.

 

 

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

 

 

반면 timestamp with time zone (이하 timestampz)은 타임존을 명시한 날짜와 시간입니다. 저장할 때는 UTC 기준 timestamp로 변환되어 저장되고, 출력할 땐 디폴트로 pg의 timezone 혹은 설정한 존으로 변환해 출력하게 됩니다. 

 

 

 

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

 

 

1. Timestampz 테스트

만약, Postgres의 timezone이 UTC이고, Timestampz를 기준으로 아래와 같이 쿼리를 한다면 어떤 결과가 나올까요?

 

 

SET Timezone = 'UTC';
SELECT TIMESTAMP WITH TIME ZONE '2023-02-19 12:00:00+09';

 

 

 

 

 

 

 

결과는 위와 같이 출력됩니다. 위에서 설명되어 있는 것처럼, 출력할 땐 디폴트로 pg의 timezone 혹은 설정한 존으로 변환해 출력하게 되는데, timezone이 UTC이기 때문에 자동으로 UTC기준으로 변환하여 출력한 것을 볼 수 있습니다. 

 

 

 

SET Timezone = 'Asia/Seoul';
SELECT TIMESTAMP WITH TIME ZONE '2023-02-19 12:00:00+10';

 

 

 

 

 

그럼, timezone을 한국으로 설정하고, 쿼리를 설정한다면 한국 시간을 기준으로 변환하여 출력하는 것을 볼 수 있습니다. 

 

 

 

 

2. Timestamp 테스트

만약, Postgres의 timezone이 UTC이고, Timestamp를 기준으로 아래와 같이 쿼리를 한다면 어떤 결과가 나올까요?

 

 

SET Timezone = 'UTC';
SELECT TIMESTAMP WITHOUT TIME ZONE '2023-02-19 12:00:00+09';

SET Timezone = 'Asia/Seoul';
SELECT TIMESTAMP WITHOUT TIME ZONE '2023-02-19 12:00:00+10';

 

 

 

 

 

 

결과는 위와 같이 출력됩니다. timestamp를 기준으로 출력하면, timezone과는 상관없이 UTC기준으로 변환하여 출력한 것을 볼 수 있습니다. 

 

 

 

 

 

 

 

---

 

 

 

AT TIME ZONE

Postgres를 사용하면서 Timezone에 대해 공부하다 보면 AT TIME ZONE이라는 개념을 살펴볼 수 있습니다. PostgreSQL에서 AT TIME ZONE은 타임존을 변환하는 데 사용되는 기능입니다. 만약 PostgreSQL을 활용해서 시간 관련 작업을 수행할 때 유용하게 사용될 수 있습니다.

 

 

AT TIME ZONE은 3가지 역할을 하는데, timestamp without time zone AT TIME ZONE zone과 timestamp with time zone AT TIME ZONE zone 두 개를 기준으로 살펴보겠습니다.

 

timestamp without time zone AT TIME ZONE 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. timestamp without time zone AT TIME ZONE zone

위에서 timestamp without time zone AT TIME ZONE zone을 활용하면 해당 zone에서의 timestamp라고 생각하고, timestamp with time zone 값을 반환한다고 설명했습니다. 예시를 통해 이 부분을 살펴보겠습니다.

 

 

 

SELECT TIMESTAMP '2023-02-19 12:00:00' AT TIME ZONE 'Asia/Seoul';

 

 

 

 

 

예시로 서울 12시에 대한 시간을 시스템 timezone인 UTC인 곳의 시간으로 조회하면 위와 같이 출력됩니다. 

 

 

timezone에서 TIMESTAMP WITHOUT TIME ZONE인 시각을 결괏값으로 전달합니다. 시스템 timezone이 UTC로 설정되어 있기에, 한국 시간의 12시는 UTC를 기준으로는 03시입니다. 그렇기에, timezone의 timestamp without time zone 시간을 조회하면 03시가 출력되는 것을 살펴볼 수 있습니다. 

 

 

 

 

2. timestamp with time zone AT TIME ZONE zone

위에서 timestamp with time zone AT TIME ZONE zone을 활용하면 timestamp with time zone 값을 해당 zone에서의 timestamp without time zone 값을 반환합니다. 예시를 통해 이 부분을 살펴보겠습니다.

 

 

SELECT TIMESTAMP WITH TIME ZONE '2023-02-19 12:00:00+10' AT TIME ZONE 'Asia/Seoul';

 

 

 

 

 

 

 

timezone을 'Asia/Seoul'로 설정했고, +10에 해당하는 타임존의 시간을 서울 시간으로 변경해서 출력됩니다. +10은 멜버른을 기준으로 시간을 조회하기에, 멜버른이 12시면 서울은 11시이기에, 위의 결과가 나오게 됩니다. 

 

 

 

 

 

 

 

 

---

 

 

 

문제 부검

처음으로 돌아와서, 그럼 created_at 컬럼의 데이터가 왜 제대로 조회되지 않았는지 파악하기 위해 실험을 했습니다.

 

 

1. timestamp with time zone

현재 게시글 테이블의 created_at 컬럼의 타입은 timestamp with time zone으로 설정되어 있습니다. 

 

 

 

 

 

 

시스템 timezone의 timezone은 UTC로 설정되어 있습니다. 만약 timestamp with time zone 타입의 컬럼을 AT TIME ZONE을 활용해서 created_at AT TIME ZONE 'Asia/Seoul'으로 설정하고 데이터를 조회하면, 해당 zone에서의 timestamp without time zone값을 반환합니다. 즉 해당 zone이 Asia/Seoul이기 때문에, 현재 저장되어 있는 created_at 컬럼의 데이터에 09시간을 더한 값이 출력되고 있었습니다.

 

 

 

비즈니스 로직으로 돌아와서, Postgres에서 조회한 값을 TypeORM으로 조회하면, 아래와 같이 출력됩니다.

 

 

created_at: 2023-02-17T23:10:26.019Z

 

 

 

TypeORM으로 조회한 값을 보면, timestamp with time zone의 값을 그대로 출력한 결과를 살펴볼 수 있습니다.

 

 

 LocalDateTime.from(nativeJs(new Date('2023-02-17T23:10:26.019Z'))).toString()

 

 

 

이 데이터를 js-joda 라이브러리를 활용해서, 서버 timezone에 맞게 변환하면 아래와 같은 결과를 살펴볼 수 있습니다. 

 

 

created_at: 2023-02-18T08:10:26.019

 

 

 

2. timestamp without time zone

그런데  댓글 테이블의 created_at 컬럼의 타입은 timestamp without time zone으로 설정되어 있을 때는 아래와 같이 출력되고 있었습니다.

 

 

 

 

위에서 timestamp without time zone AT TIME ZONE zone을 활용하면 해당 zone에서의 timestamp라고 생각하고, timestamp with time zone 값을 반환한다고 설명했습니다.

 

zone을 Asia/Seoul로 설정했기에, Asia/Seoul에서의 timestamp라고 생각하고, timestamp with time zone 값을 반환했습니다. 한국 시간의 24시는 UTC를 기준으로는 15시입니다. 그렇기에, Asia/Seoul timezone의 timestamp with time zone 시간으로 반환하여 24시였던 시간이 15시가 출력되는 것을 살펴볼 수 있습니다.

 

 

 

비즈니스 로직으로 돌아와서, Postgres에서 조회한 값을 TypeORM으로 조회하면, 아래와 같이 출력됩니다.

 

 

created_at: 2023-02-18T15:37:00.200Z

 

 

 

TypeORM으로 조회한 값을 보면, timestamp with time zone의 값을 그대로 출력한 결과를 살펴볼 수 있습니다.

 

 

LocalDateTime.from(nativeJs(new Date('2023-02-18T15:37:00.200Z'))).toString()

 

 

이 데이터를 js-joda 라이브러리를 활용해서, 서버 timezone에 맞게 변환하면 아래와 같은 결과를 살펴볼 수 있습니다. 

 

 

created_at: 2023-02-19T00:37:00.200

 

 

 

 

3. timestamp with time zone 타입 변경

그럼, 현재 댓글 테이블의 created_at 컬럼의 타입을 timestamp with time zone으로 변경해 보겠습니다.

 

 

ALTER TABLE public.comments
ALTER COLUMN created_at
TYPE timestamp with time zone 
USING created_at ::timestamp with time zone

 

 

그 후 다시 테스트를 하면 아래와 같은 결괏값이 출력됩니다.

 

 

created_at: 2023-02-19T00:37:00.200Z

 

 

timestamp without time zone으로 조회했을 땐 '2023-02-18T15:37:00.200Z' 로 나온 반면, 현재는 다르게 출력되는 것을 볼 수 있습니다.

 

 

LocalDateTime.from(nativeJs(new Date('2023-02-18T00:37:00.200Z'))).toString()

 

 

js-joda를 활용해서 타임존에 맞게 데이터를 출력하면, 아래 출력값처럼 비로소 현재 시간에 맞게 시간 데이터가 제대로 출력되는 것을 살펴볼 수 있습니다.

 

 

created_at: 2023-02-18T09:37:00.200

 

 

 

 

부검을 통해, postgres의 timestamp와 timestampz의 동작 방식 차이 때문에 문제를 겪었구나 이해할 수 있었습니다. TypeORM을 활용해서 타임존을 명확하게 지정하고 싶으신 분들은, timestampz 혹은 timestamp type을 활용해서 지정해 보시는 것을 추천드립니다. 

 

 

@CreateDateColumn({ type: 'timestamptz', nullable: false })
  createdAt: Date;

@CreateDateColumn({ type: 'timestampt', nullable: false })
  createdAt: Date;

 

 

 

타임존을 명확하게 지정하지 않고 DB 쿼리를 수행했을 때 인덱스가 적용되지 않는 문제도 있을 수 있는데요, 관련 문제가 궁금하시다면 아래 글이 궁금증 해결에 도움이 될 듯싶습니다.

 

 

 

[글또] 쿼리가 인덱스를 타지 않았던 이유.. (feat. type /timezone casting)

 

 

 

 

 

 

 

---

 

 

 

 

 

 

 

 

 

 

 

마치며

공부를 하며 그동안 아주 기초적인 것들을 제대로 공부하지 않았다는 것을 깨닫습니다. 좋은 기술을 배우는 것도 좋지만, 기술의 기반이 되는 기초적인 지식을 먼저 쌓는 것이 중요하다는 것을 다시금 깨달았습니다. 기초가 튼튼한 개발자가 되고 싶습니다.

 

 

 

 

 

 

 

---

 

 

 

 

 

출처

PSQL TIMESTAMP/AT TIME ZONE 바로 알기

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

[글또] 쿼리가 인덱스를 타지 않았던 이유.. (feat. type /timezone casting)

Postgresql 테이블 컬럼 수정

How can I create columns with type Date and type DateTime in nestjs with typeORM?

더블유클럽 기술블로그