[DB] 사용자에 따라 다른 Timezone 결과 조회하기(MariaDB)

글로벌 서비스를 지향하는 솔루션이라면, 언제나 피해갈 수 없는 것이 Timezone 문제일 것이다.

일반적으로 타임존을 다룰때는, 서버의 시간대를 사용하거나, UTC를 사용할 것이다.

 

하지만 사용자별로 Timezone을 Optional 하게 가져갈 수 있고,

설정한 타임존에 맞게 쿼리의 결과를 보여주어야 한다면 어떻게 하는것이 좋을까?

 

다양한 케이스와 환경의 차이가 있겠지만, 여기서는 내가 경험한 제한된 상황

그 환경 안에서 해결했던 방법을 적어보고자 한다.

 

직면한 환경과 제약사항은 아래과 같았다.

---

• RDBMS를 사용하고 있다. (MariaDB)
• MariaDB의 System 타임존은 UTC이다 (+00:00)
• 초당 데이터가 누적되고 있다. (unix timestamp 기준)
• 분, 시, 일 단위로 주기적으로 집계(Aggregate) 하고 있다. (UTC 기준)
• 사용자에게 타임존이 적용된 "하루"동안 집계된 데이터를 보여주어야 한다.

---

이렇게 보았을때 일반적으로 "일단위" 집계 테이블에서 조회하면 UTC 기준으로 집계되기 때문에,

사용자의 입맛대로 데이터를 가져올 수가 없다.

 

예를 들자면 아래와 같은 "시단위" 테이블에 데이터가 존재할때

UTC 기준 자정 시간으로 "일단위" 집계를 한다면 아래와 같은 결과를 얻을 수 있다.

select from_unixtime(timestamp), d.* from aggregate_day d;

하지만 사용자의 기대 sum_value 값은 150이다.

select sum(sum_value), avg(avg_value)
from aggregate_hour
where timestamp >= 1700233200  and timestamp < 1700319600;

 

결국 이미 집계되어버린 "일단위" 테이블에서 조회하는 것은 어렵고,

타임존이 영향을 받지 않은 "시단위" 테이블에서 데이터를 조회해야 할 것이다.

---

이럴때 MariaDB의 CONVERT_TZ 메서드의 도움을 받을 수 있다.

select timestamp, sum(sum_value), avg(avg_value)
from aggregate_hour
group by DATE(convert_tz(from_unixtime(timestamp), '+00:00', '+09:00'));

 

unix_timestamp의 값을 타임존을 변환한 DATE 타입으로 전환 후,

같은 DATE 값끼리 group by 연산을 통해 집계가 가능하도록 했다.

---

 

사실 RDBMS의 제약만 아니라면 훨씬 더 좋은 방법들이 많을 것이다.

제한된 상황 내에서 데이터베이스가 제공하는 기능들을 다양하게 활용하여

이슈를 해결한 케이스라 기억속에 묻어두다가 이번에 포스팅 하게 되었다.