글로벌 서비스 개발시, Mysql 날짜/시간에 처리를 위한 데이터타입 확인

1. 목적

 - Mysql은 날짜/시간과 관련된 여러 데이터 타입을 제공하는데, 글로벌 서비스를 개발시 여러 시간대 정보가 포함되었을때를 위해서 DB데이터 타입에 대한 확인

 

2. 확인을 위한 테스트 및 결과

 

-- 테스트 데이터 저장용 테이블 생성
CREATE TABLE `timezone_test` (
  `datetime` DATETIME DEFAULT NULL COMMENT 'datetime필드',
  `date` DATE DEFAULT NULL COMMENT 'date필드',
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'timestamp필드',
  `int_unixTS` INT(10) UNSIGNED DEFAULT NULL COMMENT 'unix TS를 저장하기 위한 int(고정값 저장)'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='타임존 테스트';

-- 테스트 데이터 insert
INSERT INTO timezone_test VALUES (NOW(), NOW(), NOW(), UNIX_TIMESTAMP());

-- 타임존 변경 전 저장된 데이터 확인
SELECT t.*, UNIX_TIMESTAMP(t.datetime) AS convertUNIX_TS FROM timezone_test AS t;

-- 현재 타임존 정보 확인
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;

-- 세션의 타임존 변경(예. +1시로 변경)
SET time_zone='+01:00';

-- 타임존 정보 변경 후 현재시간 확인하여 변경되었는지 확인
SELECT NOW();

-- 타임존 변경 후 저장된 값의 내용 조회
-- datetime, date필드는 DB타임존이 변경되어도 기존 값이 변경안됨, timestamp는 DB내부적으로 UTC로 저장 후 DB타임존 설정정보를 이용해서 변환하여 보여줌 -> 즉, timestmap만 타임존이 변경되었을때 영향을 받음
-- 여러 시간대를 사용한다면, 가능하면 timestamp타입으로 데이터를 저장하는게 좋음
SELECT t.*, UNIX_TIMESTAMP(t.datetime) AS convertUNIX_TS FROM timezone_test AS t;

 

 

3. 주의

 - timestamp는 2038년까지만 표시되기에 주의(예. admin시스템 만들때 무한이라는 미래값 처리할때 2999년 과 같은 값으로 처리 불가)

 

 

---

샘플: 저장된 동일 데이터를 DB타임존 변경 전과 후 비교

 - 타임존 변경 전

DB타임존 변경전의 데이터 확인

 - 타임존 변경 후

DB 타임존을 +9에서 +1로 변경 후 확인 내용. 기존 대비 timestamp만 8시간 전으로 표시됨

 

 

참고

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

MySQL :: MySQL 5.7 Reference Manual :: 11.2.2 The DATE, DATETIME, and TIMESTAMP Types

 

참고
 - 타임존 변환 사이트 : https://savvytime.com/converter/pdt-to-kst-utc/aug-1-2021/3am