MySQL에서의 시간 정보 관리하기

1. 시간정보를 관리하는 컬럼 

 1.1 데이터 타입 

시간대 정보를 포함한 시간 정보를 저장하는 데이터 타입은 TIMESTAMP 이다. 만약, 여러 시간대를 사용한다면 TIMESTAMP 데이터 타입을 사용해야 한다.  이때 TIMESTAMP 데이터 타입은 현재 입력받은 시간 정보를 UTC로 변경하여 저장하고, 나중에 보여줄 때에는 저장된 UTC 정보를 변환하여 보여주게 된다. 

TIMESTAMP 데이터 타입 외에 DATE, TIME, DATETIME 데이터 타입은 전혀 영향을 받지 않는다. 즉, 시간대 정보를 저장하지 않는다는 것이다. 

1.2 DATETIME과 TIMESTAMP 

두 타입 모두 년월일시초까지 모두 관리 가능하다. 거기다가, 소수점 6자리 까지의 초까지 저장이 가능하다.즉, YYYY-MM-DD HH:MM:SS[.fraction] 으로 가능합니다. .fraction이 소수점 6자리를 의미한다.DATETIME은 '1000-01-01 00:00:00.000000' 에서 '9999-12-31 23:59:59.999999' 의 데이터까지 저장이 가능하고,TIMESTAMP는 '1970-01-01 00:00:01.000000' UTC 에서 '2038-01-19 03:14:07.99999' UTC 까지 저장이 가능하다.

 

즉, 두 데이터 타입의 차이는 시간대 정보를 가지고 있느냐 있지 않느냐의 차이이다. 즉, DATETIME은 입력되는 날짜와 시간을 그래도 입력받는다. 하지만, TIMESTAMP는 time_zone이라는 시스템 변수로 저장된 값을 기본으로 하여 날짜와 시간정보를 입력받는다. 정확히 이야기 하면 time_zone 시스템 변수에 입력된 시간대 정보를 기반으로 데이터를 입력받아 그 정보를 UTC로 변환하여 저장한다. 즉, 저장되는 데이터 정보는 무조건 UTC 기반이다. 그리고, 입력받고 데이터를 출력할 때에는 time_zone에 입력된 값을 기반으로 변환하여 처리하는 것이다.

2. 시간대(TIME ZONE) 설정 단계 

MySQL에서는 3가지의 단계로 시간대를 설정하여 사용할 수 있다.  

-- 시스템 시간대 

-- 서버 시간대 

-- 세션 시간대 

2.1  시스템 단계에서 설정하기 

시스템 시간대는 가장 높은 단계의 시간 설정 단계로 현재 사용하는 시스템 레벨의 시간대 정보라는 것을 의미한다. 관련 정보는 system_time_zone 이라는 시스템 변수로 해당 정보를 확인할 수 있다. 시스템의 시간대 정보이기 때문에 OS에 설정된 시간대 정보를 가져와서 설정한다. 이때, system_time_zone 시스템 변수의 값은 시스템에 설정된 값으로 보여진다.  

하지만, OS가 설정한 값이 아니라 직접 사용자가 설정할 수도 있다. 시스템 단계에서 시간대를 설정하려면 mysqld_safe 의 설정 변수를 이용하여 설정해야 한다. 이때 사용하는 변수는 --timezone 이거나 my.cnf의 timezone을 사용해야 한다. 

[mysqld_safe]

timezone=UTC

그러면 사용자는 system_time_zone 시스템 변수의 값을 설정할 수 있다. 아니면 MySQL을 실행하는 OS 계정의 환경 변수로 설정하여 사용할 수도 있다. 이때 사용하는 환경 변수명은 TZ이다. 

2.2 서버 단계에서 설정하기 

서버의 시간대는 MySQL 인스턴스의 현재 시간대를 의미하는 단계이다. 시스템 변수명은 time_zone이다. time_zone 시스템 변수는 명령어로만 사용가능한 시스템 변수이다. 만약 다른 방식으로 설정하고자 한다면, --default-time-zone이나 my.cnf에 default-time-zone을 사용해야 한다. 

만약 아무런 설정도 하지 않으면 time_zone의 초기값도 SYSTEM이 된다. 

[mysqld]

default-time-zone='XXX'

명령어 수정 방식으로는 다음과 같이 처리한다.   

mysql> set global time_zone=XXX;

위와 같은 작업은 SUPER 권한이 있는 계정에서만 가능하므로, SUPER 권한이 없는 계정에서는 수정할 수 없다. 즉, 관리자만 할 수 있게 제어한다는 것이다. 

2.3 세션 단계에서 설정하기 

세션 단계에서도 시간대를 설정할 수 있다. 

mysql> set time_zone=XXX;

3. 시간대 설정 포맷 

MySQL에서 사용하는 시간대 포맷에 대해 알아보도록 하자. 

3.1  OS 시간대 정보  

OS가 설정한 시간대 정보가 보여진다. 이때는 MySQL에 특별한 시간대를 설정하지 않으면 보여지는 것으로, 한국에서는 기본적으로 리눅스 OS에서 KST로 설정하기 때문에 다음과 같이 보여지게 될 것이다. 

| time_zone                       | SYSTEM            |

| system_time_zone                | KST               |

 

MySQL의 시간대는 SYSTEM 즉, OS의 값을 가져다가 사용하는데, 해당 시스템의 OS가 설정한 값은 KST라는 것이다. 

3.2 UTC 기준의 차이값을 이용한 설정 

UTC를 기준으로 하여 사용하려는 시간대가 가지는 시간차를 직접 설정하여 사용하는 것을 말한다. 

즉, '+00:00' '+10:00' '-6:00" 이와 같이 숫자로 지정하는 방식을 말한다. 

3.3 Named Time Zone 정보를 사용한 설정 

'Asia/Seoul' , 'US/Eastern' or 'Europe/Paris' 와 같이 지정한 문자열을 사용하여 지정하는 방식을 말한다. 이렇게 사용하고자 할 때에는 MySQL에 시간대 정보 테이블을 로딩 시켜놔야 한다. 

3.3.1 시간대 정보 로딩하기. 

MySQL은 MySQL이 사용할 수 있는 시간대 정보 데이터를 제공해 주고 있다. 

다음의 사이트에 가면 제공해주는 데이터를 확인하여 받을 수 있다. 

http://dev.mysql.com/downloads/timezones.html

4. 시간대 변경시 영향을 받는 컬럼 타입 및 함수.

4.1 컬럼 타입 

앞에서도 이야기 했다시피, 시간대 정보를 변경하게 되면 TIMESTAMP 타입의 데이터만 영향을 받는다. 

4.2 함수

현재의 시간대 정보 변경에 영향을 받는 함수는 NOW()와 CURTIME() 과 같은 함수만 영향을 받는다. 즉, UTC_TIMESTAMP() 함수는 전혀 영향을 받지 않는다. 

5. MySQL Ver. 5.6 추가 기능 

5.1 자동으로 시간 데이터 입력하기 

MySQL Ver. 5.6 부터 DATETIME 에 기본으로 시간과 날짜값이 들어가게 설정할 수 있다. 즉, DATETIME과 TIMESTAMP 로 설정된 컬럼에 지정하지 않고 자동으로 값이 입력되게 할 수 있다.

CREATE TABLE t1 (

  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

입력할때 기본값도 가능하고, 수정될때 다시 그 시간대로 수정되는 것도 가능하다.여기서 current_timestmap는 now()로 나오는 값과 동일한 값이 입력되는 keyword이다.

6. MySQL Ver. 5.7 추가 기능 

6.1 log_timestamps 시스템 변수 추가 

MySQL Ver. 5.7.2 부터 log_timestamps 라는 시스템 변수가 추가되었다. 

이 시스템 변수는 현재 데이터로 사용하는 시간대와 에러로그와 general query log , slow query log 파일의 시간 정보를 다르게 가져가고자 할때 사용할 수 있는 시스템 변수이다. 즉, 데이터는 UTC 시간대 정보를 기준으로 사용하지만, 로그 파일들은 현재 DB서버가 위치한 로컬의 시간대로 작성되게 하고자 하는 경우에 사용이 가능하다. 

이 시스템 변수는 운영중에 변경이 가능하고, 값은 SYSTEM, UTC 둘 중 하나만 선택이 가능하다. 

참고로 log_timestamps 시스템 변수는 mysqld_safe나 TZ 환경 변수를 이용하여 시스템 레벨의 타임존을 설정한 경우에는 전혀 동작하지 않는다. 인스턴스 레벨로 설정한 경우에만 동작함을 유의하자.