DB 데이터 타입 - 날짜와 시간

15.3 날짜와 시간

MySQL에서는 날짜만 저장하거나 시간만 따로 저장할 수도 있으며, 날짜와 시간을 합쳐서 하나의 칼럼에 저장할 수 있게 여러 가지 타입을 지원한다.
MySQL의 날짜 타입은 자체적으로 타임존을 관리하지 않으므로 DATETIME이나 DATE 타입은 현재 DBMS 커넥션의 타임존에 관계없이 클라이언트로부터 입력된 값을 그대로 저장하고 조회할 때도 변환없이 그대로 출력한다. 하지만 TIMESTAMP는 항상 UTC 타임존으로 저장되므로 타임존이 달라져도 값이 자동으로 보정된다.

예제) 한국에 있는 사용자가 DATETIME 타입과 TIMESTAMP 타입에 저장한 날짜 값을 미국의 로스엔젤레스의 사용자가 조회하는 과정을 보여준다.

CREATE TABLE tb_timezone(fd_datetime DATETIME, fd_timestamp TIMESTAMP);
// 현재 세션의 타임존을 한국으로 변경
SET time_zone='Asia/Seoul';
// 한국 시간 추가
INSERT INTO tb_timezone VALUES(now(), NOW());
// 저장된 시간 확인
SELECT * FROM tb_timezone;
// 같은 커넥션에서 타임존을 미국의 로스엔젤레스로 변경
SET time_zone='America/Los_Angeles';
// 타임존이 미국 로스엔젤레스로 변경된 상태로 한국의 타임존으로 입력된 일시 정보를 확인
SELECT * FROM tb_timezone;

DATETIME에 저장된 날짜와 시간 정보는 커넥션의 타임존이 한국에서 미국의 로스엔젤레스로 변경돼도 전혀 차이가 없다.
TIMESTAMP 칼럼은 타임존이 변경됨에 따라 그에 맞게 시간이 보정된다.
만약 글로벌하게 사용되는 소프트웨어를 개발 중이라면 반드시 각 나라별로 날짜와 시간 정보, 그리고 타임존에 관련된 정보까지 관리해야 한다. 이때 TIMESTAMP 타입을 사용한다면 커넥션의 타임존 설정만 제대로 된다면 특별히 문제되지는 않지만 DATETIME 타입은 커넥션의 타임존 변경으로 보완되지 않는다. 그래서 결국은 미국이나 캐나다의 사용자들은 미래에 작성된 게시물을 보게되는 결과가 발생할 수도 있다.
가장 좋은 방법은 DATETIME의 값은 항상 MySQL 서버의 타임존으로 변환해서 저장하는 것이다. 그리고 프로그램에서 사용하는 MySQL 커넥션의 타임존은 그 지역에 맞게 정확하게 설정하고, 날짜나 시간 값을 저장할 때는 CONVERT_TZ()와 같은 함수로 로컬 시간은 MySQL 서버의 타임존으로 변환해서 저장하는 것이다. 물론 DATETIME이나 DATE 타입의 값을 조회할 때도 CONVERT_TZ()함수로 변환하는 작업이 필요하다. 하지만 TIMESTAMP 칼럼의 값은 자동으로 커넥션의타임존에 의해 변환된다.
TIMESTAMP 타입의 값은 MySQL 서버의 타임존에 의존적이지 않고 항상 UTC로 저장되므로 MySQL 서버의 타임존을 변경한다고 해서 별도로 변환 작업을 해줄 필요는 없다.

15.3.1 TIMESTAMP 타입의 옵션

TIMESTAMP 타입이 지닌 또 하나의 차이는 TIMESTAMP 타입의 칼럼 값은 레코드가 UPDATE되거나 INSERT될 때 자동으로 현재 시간으로 변경된다. TIMESTAMP 타입의 이름대로 각 레코드의 INSER나 UPDATE 시점의 도장을 찍는 역할을 한다.
칼럼을 만들어두면 특별히 TIMESTAMP 타입의 값을 변경하지않고 다른 칼럼의 값만 변경했는데도 변경된 시점의 시간으로 업데이트 된다.

TIMESTAMP 타입의 가장 큰 특징

TIMESTAMP 타입의 칼럼이 하나만 존재하는 테이블에서는 레코드가 INSERT되거나 UPDATE되는 시점의 시간이 TIMESTAMP 타입의 칼럼에 자동으로 업데이트된다. 하지만 하나의 테이블에 2개 이상의 TIMESTAMP 칼럼이 모두 아무런 옵션을 가지지 않으면 테이블에서 먼저 명시된 칼럼만 진짜 타임스탬프 역할을 하고 나머지 TIMESTAMP 타입의 칼럼은 타임 스탬프의 기능을 잃게 된다.
타임스탬프 타입의 역할을 가진 칼럼이든 아니든, 사용자가 명시적으로 특정 시간을 저장하면 시간 도장의 기능과 관계없이 사용자가 명시한 값이 저장된다.

// TIMESTAMP 칼럼의 두 가지 옵션을 모두 적용한 예제다. 
// 레코드가 초기 생성될 때 col_ts 칼럼에 값이 직접 입력되지 않으면 자동으로 현재 시간으로 저장되며, 레코드의 다른 칼럼이 변경되는 시점마다  col_ts 칼럼의 값은 변경 시점의 시간으로 자동업데이트 된다.
// 만약 TIMESTAMP 칼럼을 정의하면서 TIMESTAMP 칼럼에 아무런 옵션을 명시하지 않으면 이 두가지 옵션을 묵시적으로 정의한 것으로 간주된다.
col_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

// TIMESTAMP 칼럼을 생성할 때 "DEFAULT CURRENT_TIMESTAMP" 옵션만 부여되면 UPDATE 문장을 실행할 때 시간이 자동으로 업데이트되는 옵션은 비활성화되며, 레코드가 초기 생성되는 시점에만 현재 시간이 col_ts 칼럼에 저장된다.
col_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

// "DEFAULT CURRENT_TIMESTAMP" 옵션과는 반대로, TIMESTAMP 칼럼의 옵션으로 "ON UPDATE CURRENT_TIMESTAMP"만 명시되면 초기 기본값의 설정은 비활성화되고, 레코드가 처음 생성되는 시점의 기본값은 자동으로 저장되지 않고 변경될 때만 col_ts 칼럼에 변경 시점의 시간이 저장된다.
col_ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

// TIMESTAMP 칼럼의 두 가지 옵션을 모두 사용하지 않으려면 이와 같이 다른 옵션 없이 "DEFAULT 0"옵션을 부여하면 된다. 여기서는 초기 디폴트 값을 0으로 설정했지만 "DEFAULT '2011-07-02 12:00:00'"와 같은 상수 값을 설정해도 된다.
col_ts TIMESTAMP DEFAULT 0

15.3.2 타임존 등록 및 사용

타임존이 제대로 등록됐는지 확인하는 방법은 CONVERT_TZ라는 함수를 이용해 타임존을 변경한 뒤 시간을 확인해보면 된다.
타임존 정보를 MySQL 서버에 등록하지 못했거나 이 작업이 귀찮다면 “+09:00”과 같이 타임존의 이름이 아닌 시간차를 그대로 타임존으로 설정할 수도 있다.
“-08:00”과 같이 표시된 시간차를 사용하면 가독성이 떨어지므로 가능하면 타임존 정보를 MySQL 서버에 적재해서 사람이 읽을 수 있는 형태로 타임존을 설정하는 것이 좋다.