24. DATETIME과 TIMESTAMP 사용법

(MySQL5.6 기준으로 정리한 내용이다.)

1. 관리하는 데이터
두 타입 모두 년월일시초까지 모두 관리 가능하다. 거기다가, 소수점 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 까지 저장이 가능하다.
 
2. DATETIME과 TIMESTAMP의 차이점
즉, 두 데이터 타입의 차이는 시간대 정보를 가지고 있느냐 있지 않느냐의 차이이다. 즉, DATETIME은 입력되는 날짜와 시간을 그래도 입력받는다. 하지만, TIMESTAMP는 time_zone이라는 시스템 변수로 저장된 값을 기본으로 하여 날짜와 시간정보를 입력받는다. 정확히 이야기 하면 time_zone 시스템 변수에 입력된 시간대 정보를 기반으로 데이터를 입력받아 그 정보를 UTC로 변환하여 저장한다. 즉, 저장되는 데이터 정보는 무조건 UTC 기반이다. 그리고, 입력받고 데이터를 출력할 때에는 time_zone에 입력된 값을 기반으로 변환하여 처리하는 것이다.
 
3. 자동으로 기본값이 들어가게 설정하기
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이다.
 
4. UTC 데이터로 TIMESTAMP 값 입력하기
UTC 시간대로 TIMESTAMP값을 입력하는 방법은 여러가지가 있다.여기서는 시간대 정보를  mysql 스키마에 로딩하지 않고 처리하는 방법만을 정리한다.

4.1 함수 이용하기

[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)
 
[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:41:50 |
+---------------------+
1 row in set (0.00 sec)
 
[root@oraclelinux6.localdomain][test]> create table test ( ts timestamp not null) ;
Query OK, 0 rows affected (0.01 sec)
 
[root@oraclelinux6.localdomain][test]> insert into test( ts) values (utc_timestamp());
Query OK, 1 row affected (0.00 sec)
 
[root@oraclelinux6.localdomain][test]> select * from test;
+---------------------+
| ts                  |
+---------------------+
| 2015-06-25 06:41:39 |
+---------------------+
1 row in set (0.00 sec)

4.2 time_zone 시스템 변수 값 변경하기

[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)
 
[root@oraclelinux6.localdomain][test]> set session time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
 
[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 06:44:20 |
+---------------------+
1 row in set (0.01 sec)
 
[root@oraclelinux6.localdomain][test]> set session time_zone='SYSTEM';
Query OK, 0 rows affected (0.00 sec)
 
[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:45:39 |
+---------------------+
1 row in set (0.00 sec)
 
[root@oraclelinux6.localdomain][test]> system date;
Wed Jun 24 23:45:47 PDT 2015
[root@oraclelinux6.localdomain][test]>