postgresql at time zone 에 대해 알아봅시다.

 postgres에서 at time zone 을 쓰는 방법을 간단하게 알아봅시다. 정말이지 시간대는 실수 하기도 정말 좋고, 삽질 하기도 너무 좋은 듯 합니다.

 

---

 먼저, 쿼리를 실행할 때 리턴하는 type 정보들을 알아봅시다. pg_typeof로 조회할 수 있는데요.

 

 요래 입력해 봅시다.

 

 그러면, now() at time zone 'HKT'의 리턴 타입은 timestamp without time zone이고, now()의 리턴 타입은 timestamp with time zone임을 알 수 있습니다.

 

 

 먼저, timezone을 보겠습니다.

 

 Asia/Seoul입니다. 서버 시간이 서울 시간대인 셈입니다.

 

 

 now() at time zone 'HKT'의 결과는 어떻게 될까요? 먼저 postgres에서 now()는 문서에 따르면 time with timezone을 돌려줍니다. 현재 제 시간대는 서울이니, 2022-09-02 22:38:17+09:00이 리턴될 겁니다. 여기에, at time zone 'HKT'가 들어가게 되는데요. 홍콩은 서울보다 1시간 늦습니다.

 

 따라서, 아래와 같이 변환이 됩니다.

 

 

 결과는 위와 같습니다. 정리하면, 시간대 정보가 있는 timestamp at time zone ZONE의 경우, ~ 시간대에 YYYY년 MM월 DD일 hh 시 mm분 ss 초이다. 라는 정보가 있을 겁니다. 이 정보를 시간대 ZONE의 시간으로 바꿉니다. 그리고 시간대 정보를 빼 버립니다. 그래서, utc 시간대로 저장된 정보를, 서울 시간으로 빼거나, 혹은 뉴욕 시간으로 뺄 때 유용하게 쓰일 수 있습니다.

 

 

---

 이제, 이 쿼리를 생각해 봅시다. 서버 시간대는 서울이라 가정합시다. 그리고 저는 괌을 기준으로 2022년 9월 3일에 쓴 게시물들을 모두 가지고 오고 싶습니다. 어떻게 하면 좋을까요?

 

 

포스트는 서울 시간으로 2022년 9월 2일 23시 22분에 쓰여졌습니다. 괌을 기준으로는 2022년 9월 3일에 쓰여진 것인데요.

 

 

 created를 괌 시간으로 바꿉니다. 그러면, 서울 시간으로 2022년 9월 2일 23시 22분 34초에 쓰여진 post는 괌 기준으로 9월 3일 0시 22분 34초에 쓰여진 셈이 됩니다.

 

 

 따라서, 괌 기준으로 9월 3일 이후에 쓰여진 글은 위 쿼리와 같이 입력하시면 되겠습니다.

 

 

---

 이제, timestamp 정보만 주어지는 경우에는 어떨까요?

 

 다시 문서를 보면, 1번 항목임을 알 수 있어요. 주어지는 시각 2022-09-03 00:00:00이 'America/Denver'의 시간대라고 가정합니다.

 

 

 time zone 정보가 없는 시간대가 들어왔고, at time zone 덴버라고 되어 있었습니다. 규칙 1에 따르면, 결과물은 시간대 값이 들어간 timestamp 값입니다. 그리고, 제 디비 서버 시간대가 seoul로 설정되었습니다. 따라서, 이 시간을 서울 시간대로 바꾸게 되는데요. 덴버 시간은 서울 시간보다 15시간 늦습니다.

 

 따라서, 덴버 시간으로 0시라면, 서울 시간으로는 15시입니다.

 

 

 따라서 요래 수행되게 됩니다. 그리고, 쿼리의 결과는 아래와 같습니다.

 

 

 여기까지 어렵지 않네요. 요약. timezone이 없는 timestamp 값이 들어왔다. 그리고 at time zone ZONE이 뒤에 붙었다. 그러면 timestamp 시간이, ZONE 시간대라고 가정하게 됩니다.

 

---

 

이제 이건 어떨까요? 조금 문제가 복잡한 거 같습니다. 현재 서버에 설정되어 있는 시간대는 서울 시간대입니다. 서울 시간대이고, 9월 3일 오전 1시 35분입니다. 2번째 줄을 보면, now() at time zone 뉴욕이라고 나와 있는데요. 참고로 뉴욕은 서울보다 13시간 늦습니다. 고로, date 안에 있는 내용을 수행하게 되면, 9월 2일 오후 12시 35분이 될 겁니다. 이것을 date에 넣으니 9월 2일이 나올 겁니다. 여기까지 정리하면 아래와 같습니다.

 

 

 now()는 타임존이 있는 type을 리턴합니다. 서버는 서울 시간대로 설정되었으니, 뉴욕은 9월 2일 12:35일 겁니다. 이 중 date만 뽑으라고 했기 때문에 09-02가 리턴되게 됩니다. 그 다음에는 어떻게 될까요? 3번째 줄 때문에 New york의 시간으로 9월 2일 00시 00분으로 출력될 것만 같습니다.

 

 그런데 사실 출력되는 값은 아래와 같습니다.

 

 

 이건 왜 그럴까요? 문서를 보시면 any type containing only date or time 부분이 있는데요. 이 타입에 대해서는 설정되어 있는 local zone으로 가정한다고 되어 있습니다. 제 서버는 사실, 서울 시간대였기 때문에

 

 

 위와 같은 로직을 타게 됩니다. 하아.. 의도대로 동작하게 하려면 어떻게 해야 할까요?

 

 

 이렇게 하면 어떤가요? date 2022-09-02를 timestamp으로 바꾸었습니다. 그런데, 이건 without_timezone입니다. 타임존이 없죠? 따라서 뉴욕 기준으로 2022-09-22 00:00:00이다. 라는 정보를 리턴하게 됩니다.

 

 

 어? 그런데 왜 13시가 리턴되죠? 잘못된 거 아닌가요? 하실 수 있지만, without_timezone인 경우, at time zone ZONE을 붙이게 되면, 시간대가 ZONE이라 가정합니다. 뉴욕 시간대 0시 0분이고, 서버는 서울 시간대로 설정되었습니다. 서울은 뉴욕보다 13시간 빠르기 때문에, 9월 2일 13시 0분 0초가 출력되게 됩니다.