postgres 에서도, timezone이 붙은 timestamp와, 그렇지 않은 timestamp가 있어요. 이 글에서는, at time zone 연산이 어떻게 동작하는지 알아봅시다.
timezone 정보가 있는 시간을 로컬 시간대로 바꾸기
먼저, now 함수는 timezone 정보를 포함한 timestamp를 돌려줍니다.
now 함수를 실행해 보겠습니다. 그러면 현재 시간이 출력 되는데요. 뒤에 붙는 정보를 봅시다.
4월 27일 17시 24분 45초 뒤에 붙은 +0900 이 있습니다. 이는 utc 기준 시간으로부터 9시간 빠르다는 뜻입니다. 시간대 정보가 있는 셈이지요. 이 정보를 지역 로컬 시간으로 바꾸기 위해서는 어떻게 할까요? 이 때에는 아래와 같이 써 주면 됩니다.
tz_in_timezone at time zone timezone_name
timezone_name 시간으로 출력합니다.
당연하게도 시간대 정보가 있기 때문에, 지역 정보가 주어지면, 올바른 로컬 시간을 알려줄 수 있습니다. 뉴욕 지역 시간으로 바꾸어 봅시다.
pg_timezone_names 테이블은 시간대 이름과 해당 시간대의 정보들을 가지고 있는 테이블입니다. 그림 3의 쿼리는 name에 New_York가 들어가는 결과를 찾습니다.
쿼리의 결과를 보면, name이 있고, utc_offset이 있습니다. 이름은 America/New_York입니다. 뉴욕 시간을 뽑으려면, at time zone 뒤에 ‘America/New_York’를 입력해 주면 됩니다. 다음에 utc_offset이 있는데요. 기준 시각으로부터 얼마만큼 빠른지, 느린지 알려줍니다. -04:00:00 이므로, 4시간 느리다고 할 수 있어요.
서울은 9시간 빠르니까, 서울과 비교하면 13시간이 느립니다.
now는 timestamp with timezone을 리턴합니다. select now() at time zone ‘America/New_York’ 쿼리를 수행해 보겠습니다. 그러면 아래와 같은 일이 벌어집니다.
- 시간대 정보를 토대로, 뉴욕 지역 시간으로 바꿉니다.
- timezone 정보를 제거합니다.
결과는 아래와 같습니다.
서울 시간은 오후 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 을 입력하면, 현재 시간대를 볼 수 있어요.
Asia/Seoul로 되어 있군요. 아래 쿼리는 어떻게 실행될까요?
보면, timestamp가 주어졌는데요. timezone 정보가 없습니다. 그냥 2020년 1월 1일 0시 0분 0초라는 정보만 있는 상태입니다. common case인 text value로 주어졌습니다. 이 경우, 아래와 같이 동작하게 됩니다.
- 2020년 1월 1일 0시 0분 0초이다.
- 주어진 시간대 정보가 Shanghai 정보이다.
고로, 이 쿼리는 아래와 같은 결과를 내보내게 됩니다.
상하이 시간대는 서울 시간대보다 1시간 늦습니다. 따라서, 2020년 1월 1일 1시 +0900 이라는 정보가 출력됩니다. 이를 이용해서, 특정 지역 시간을 다 지역 시간으로 바꿀 수 있습니다.
예를 들어, 위 쿼리는 Shanghai 시간을 Guam 시간으로 바꾸는 쿼리입니다. 이 쿼리는 아래를 의미해요.
- 2020년 1월 1일 0시 0분 0초인데요.
- 상하이 시간이에요.
- 이 정보를 Guam 지역 시간으로 바꿀 거에요.
결과를 보겠습니다.
2020년 1월 1일 2시가 나옵니다. 괌은 서울 시간보다 1시간이 빠르고, 서울은 상하이보다 1시간이 빠르니 결과가 2시로 나오게 됩니다.