MySQL 타임존 다루기

 

 데이터베이스에 시각 (날짜) 관련 데이터를 저장하려다 보면 타임존에 대해 고민하게 된다. 국내 한정으로 서비스한다면 크게 고려하지 않는 부분이나, 글로벌 서비스를 고민한다면 필히 타임존을 고려하여 날짜데이터를 다루게 된다. 이번에는 MySQL 데이터베이스를 기준으로 날짜 데이터와 타임존을 어떻게 다룰 수 있는지 알아본다.

 

 

목차 

• MySQL의 날짜와 시간 
• DATETIME과 TIMESTAMP 다루어보기

 

---

MySQL의 날짜와 시간 

 

MySQL의 날짜 타입 

• DATETIME, TIME : 컬럼 자체에 타임존 정보 없음. 클라이언트가 입력한 값 그 자체를 저장, 반환 
• TIMESTAMP : UTC로 저장되어 클라이언트에게 반환할 때는 타임존 변환을 거쳐 반환 

 

MySQL이 관리하는 타임존 정보는 아래와 같다. 

• system_time_zone : 서버의 타임존 정보 (기본적으로 OS의 타임존 정보를 따름) 
• time_zone : 세션 (커넥션)별 타임존 정보

(Mysql 서버 시작시 --timezone, --default-time-zone 옵션으로 타임존 설정 가능)

---

DATETIME과 TIMESTAMP 다루어보기

글로벌 서비스의 알림 스케쥴을 만드는 테이블을 생성한다. 

schedule_alert 테이블 생성

 

내 Mysql 데이터베이스의 타임존 정보는 아래와 같다.

 

생신 축하 알림을 보내는 데이터를 넣고 확인해 보면,

+---------+-------------------+-------------------+
|name     |alert_dt           |alert_ts           |
+---------+-------------------+-------------------+
|생일축하해요 엄마|2024-10-25 00:00:00|2024-10-25 00:00:00|
+---------+-------------------+-------------------+

 

뉴욕에서 접속한다면 어떻게 될까 

+---------+-------------------+-------------------+
|name     |alert_dt           |alert_ts           |
+---------+-------------------+-------------------+
|생일축하해요 엄마|2024-10-25 00:00:00|2024-10-25 00:00:00|
+---------+-------------------+-------------------+

 

뉴욕 세션으로 조회하면 알람시각 데이터 (alert_ts)가 늦게 나온다. 

 따라서 아래처럼 세션의 타임존을 알맞게 수정해 준다. 그럼 UTC 기반의 TIMESTAMP 컬럼은 내 세션의 타임존 정보를 기반으로 컨버팅 하여 보여준다.

+---------+-------------------+-------------------+
|name     |alert_dt           |alert_ts           |
+---------+-------------------+-------------------+
|생일축하해요 엄마|2024-10-25 00:00:00|2024-10-24 11:00:00|
+---------+-------------------+-------------------+

 

이번엔 크리스마스 알림을 설정해 보자 

 

 

뉴욕에서 크리스마스 알림 시각이 잘못 조회된다. 

+--------+-------------------+-------------------+
|name    |alert_dt           |alert_ts           |
+--------+-------------------+-------------------+
|메리 크리스마스|2024-12-25 00:00:00|2024-12-24 11:00:00|
+--------+-------------------+-------------------+

 

 TIMSTAMP는 타임존 정보를 기반으로 UTC 데이터를 변환해서 반환한다. 위처럼 나오는 이유는 뉴욕 타임존 (현재 나의 세션 타임존 정보)에 맞게 변환되어 나오기 때문이다. 따라서 아래처럼 MySQL 함수 CONVERT_TZ()로 현재 session_time_zone에서 변환하려는 타임존으로 보정할 수 있다.

 

 

+-------------------+--------+-------------------+
|alert_ts           |name    |alert_dt           |
+-------------------+--------+-------------------+
|2024-12-25 00:00:00|메리 크리스마스|2024-12-25 00:00:00|
+-------------------+--------+-------------------+

 

 Hibernate나 MyBatis와 같은 라이브러리는 내부적으로 디비 반환값을 자동으로 페치 해서 반환한다. (JVM 시스템의 타임존에 따라) 따라서 DATETIME 타입 컬럼일지라도 애플리케이션 타임존 정보에 의존하여 타임존 변환하여 반환하는 케이스들이 있다. 실제로 테스트를 해보는 것이 중요하다.

 최대한 응용프로그램에서 시간 정보를 강제로 타임존 변환을 하거나 MySQL 서버의 SQL 문장으로 CONVERT_TZ()를 이용해 타임존 변환을 하지 않도록 하자. 타임존 관련 설정은 한 번 문제가 되기 시작하면 매우 해결하기가 어려운 문제가 될 수도 있다.

*Real MySQL 8.0 2권 발췌

---

참고

https://product.kyobobook.co.kr/detail/S000001766483

Real MySQL 8.0 (2권) | 백은빈 - 교보문고

 

https://dev.mysql.com/doc/refman/8.4/en/datetime.html

MySQL :: MySQL 8.4 Reference Manual :: 13.2.2 The DATE, DATETIME, and TIMESTAMP Types

https://dev.mysql.com/doc/refman/8.4/en/time-zone-support.html

MySQL :: MySQL 8.4 Reference Manual :: 7.1.15 MySQL Server Time Zone Support