Home » SQL » POSTGRES » postgres at time zone 연산에 대해 알아봅시다.

postgres at time zone 연산에 대해 알아봅시다.

postgres 에서도, timezone이 붙은 timestamp와, 그렇지 않은 timestamp가 있어요. 이 글에서는, at time zone 연산이 어떻게 동작하는지 알아봅시다.


timezone 정보가 있는 시간을 로컬 시간대로 바꾸기

먼저, now 함수는 timezone 정보를 포함한 timestamp를 돌려줍니다.

[그림 1] now 함수 실행하기

now 함수를 실행해 보겠습니다. 그러면 현재 시간이 출력 되는데요. 뒤에 붙는 정보를 봅시다.

[그림 2] 뒤에 붙은 +0900

4월 27일 17시 24분 45초 뒤에 붙은 +0900 이 있습니다. 이는 utc 기준 시간으로부터 9시간 빠르다는 뜻입니다. 시간대 정보가 있는 셈이지요. 이 정보를 지역 로컬 시간으로 바꾸기 위해서는 어떻게 할까요? 이 때에는 아래와 같이 써 주면 됩니다.

tz_in_timezone at time zone timezone_name

timezone_name 시간으로 출력합니다.

당연하게도 시간대 정보가 있기 때문에, 지역 정보가 주어지면, 올바른 로컬 시간을 알려줄 수 있습니다. 뉴욕 지역 시간으로 바꾸어 봅시다.

[그림 3] New_York 시간대 이름 찾기

pg_timezone_names 테이블은 시간대 이름과 해당 시간대의 정보들을 가지고 있는 테이블입니다. 그림 3의 쿼리는 name에 New_York가 들어가는 결과를 찾습니다.

[그림 4] 쿼리의 결과

쿼리의 결과를 보면, name이 있고, utc_offset이 있습니다. 이름은 America/New_York입니다. 뉴욕 시간을 뽑으려면, at time zone 뒤에 ‘America/New_York’를 입력해 주면 됩니다. 다음에 utc_offset이 있는데요. 기준 시각으로부터 얼마만큼 빠른지, 느린지 알려줍니다. -04:00:00 이므로, 4시간 느리다고 할 수 있어요.

서울은 9시간 빠르니까, 서울과 비교하면 13시간이 느립니다.

[그림 5] New_York 시간대로 바꾸기

now는 timestamp with timezone을 리턴합니다. select now() at time zone ‘America/New_York’ 쿼리를 수행해 보겠습니다. 그러면 아래와 같은 일이 벌어집니다.

  • 시간대 정보를 토대로, 뉴욕 지역 시간으로 바꿉니다.
  • timezone 정보를 제거합니다.

결과는 아래와 같습니다.

[그림 6] 쿼리의 실행 결과

서울 시간은 오후 6시 46분인데, 오전 5시 46분이 출력 되었어요. 뉴욕이 서울보다 14시간 느린 걸로 알고 있는데, 어떻게 된 것일까요? 서머 타임이 적용되었기 때문이에요.


timezone 정보가 빠진 경우

만약에 timezone 정보가 빠진 경우에는 어떻게 동작할까요? postgres at time zone 연산자는 어떻게 동작할까요? 뒤에 오는 timezone_name 시간이라고 가정합니다. 그리고, timezone 정보가 붙은 시간 정보를 돌려주게 됩니다.

tz_without_timezone at time zone timezone_name

시각 tz_without_timezone은 지역 timezone_name 의 시간이다.

show timezone 을 입력하면, 현재 시간대를 볼 수 있어요.

[그림 7] 현재 시간대 보기

Asia/Seoul로 되어 있군요. 아래 쿼리는 어떻게 실행될까요?

[그림 8] 문제의 쿼리

보면, timestamp가 주어졌는데요. timezone 정보가 없습니다. 그냥 2020년 1월 1일 0시 0분 0초라는 정보만 있는 상태입니다. common case인 text value로 주어졌습니다. 이 경우, 아래와 같이 동작하게 됩니다.

  • 2020년 1월 1일 0시 0분 0초이다.
  • 주어진 시간대 정보가 Shanghai 정보이다.

고로, 이 쿼리는 아래와 같은 결과를 내보내게 됩니다.

[그림 9] 쿼리의 결과

상하이 시간대는 서울 시간대보다 1시간 늦습니다. 따라서, 2020년 1월 1일 1시 +0900 이라는 정보가 출력됩니다. 이를 이용해서, 특정 지역 시간을 다 지역 시간으로 바꿀 수 있습니다.

[그림 10] 상하이 시간을 괌 시간으로 바꾸기

예를 들어, 위 쿼리는 Shanghai 시간을 Guam 시간으로 바꾸는 쿼리입니다. 이 쿼리는 아래를 의미해요.

  • 2020년 1월 1일 0시 0분 0초인데요.
  • 상하이 시간이에요.
  • 이 정보를 Guam 지역 시간으로 바꿀 거에요.

결과를 보겠습니다.

[그림 11] 쿼리의 결과

2020년 1월 1일 2시가 나옵니다. 괌은 서울 시간보다 1시간이 빠르고, 서울은 상하이보다 1시간이 빠르니 결과가 2시로 나오게 됩니다.

Leave a Comment

4 × 2 =