[SQL] 날짜형 데이터 - DATETIME과 TIMESTAMP 차이를 중점으로 (MySQL)

데이터베이스에서 필수로 쓰이는 날짜형 데이터에 대해 정리해두고자 합니다.

4가지 유형의 데이터 타입에 대한 정리와 일자와 시간을 모두 표현하는 DATETIME과 TIMESTAMP의 차이를 알아보겠습니다.

1. DATE, DATETIME, TIMESTAMP, TIME

MySQL에서는 일자와 시간을 표현하기위해 DATE, DATETIME, TIMESTAMP, TIME 타입을 사용할 수 있습니다.

	DATE	DATETIME	TIMESTAMP	TIME
상세	일자만 표현	- 일자와 시간 표현 - 최대 마이크로초 정밀도의 소수 초를 포함 가능 - 현재 날짜와 시간으로 자동 초기화 및 업데이트가 가능	- 일자와 시간 표현 - (default) NOT NULL - Timezone 기반 - 최대 마이크로초 정밀도의 소수 초를 포함 가능 - 현재 날짜와 시간으로 자동 초기화 및 업데이트가 가능	시간만 표현
형식	YYYY-MM-DD	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss	hh:mm:ss
범위	'1000-01-01' ~'9999-12-31'	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	'1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC	'-838:59:59' ~'838:59:59'
용량	3byte	8byte	4byte	3byte

 

일자와 시간을 모두 표현하는 DATETIME과 TIMESTAMP는 얼핏봐서는 비슷한데, 차이점이 있습니다. 

이 둘의 차이를 알고 있어야 예상치 못한 에러사항을 대비할 수 있기 때문에, 차이점을 한 번 알아보겠습니다.

2. DATETIME vs TIMESTAMP

1) 범위

일자와 시간을 모두 표현하는 두 타입은 일단 표에서 볼 수 있듯 범위에서 차이가 있습니다.

DATETIME의 범위는 '1000-01-01 00:00:00' 부터 '9999-12-31 23:59:59' 로, 시간대에 관계없이 절대적인 일자와 시간을 저장합니다.

TIMESTAMP의 범위는 '1970-01-01 00:00:01' UTC 부터 '2038-01-19 03:14:07' UTC 로, 데이터가 UTC로 변환되어 저장되며 검색시 현재 시간대로 변환이 됩니다. TIMESTAMP의 범위는 Unix 시간과 관련이 있어, 2038년 문제로 인해 최대 범위가 2038년까지로 제한됩니다.

2) Timezone의 적용 여부

두 타입은 Timezone의 적용 여부에 차이가 있습니다.

Timezone은 동일한 시간을 따르는 지역을 의미하고, 해당 국가에 의해 법적으로 지정되는 값입니다.

UTC, Asia/Seoul 등의 표기가 해당 시간이 어떤 로컬을 기준으로 작성한 것인지를 명시하는 것입니다.

 

MySQL은 TIMESTAMP 값을 현재 시간대에서 *UTC로 변화하여 저장하고, 다시 UTC에서 현재 시간대로 변환하여 검색합니다.

*UTC: 국제 표준시

기본적으로 설정되는 시간대는 서버 시간으로, 시간대 설정이 일정하게 유지된다면 저장한 값과 동일한 값을 반환받습니다.

만약 TIMESTAMP 값을 저장한 후, 시간대를 변경하고 값을 조회하면, 조회 결과는 저장한 값과 다르게 됩니다. 시간대 변환시 동일한 시간대를 사용하지 않았기 때문에 이러한 결과가 발생하는 것입니다.

*현 시간대는 시스템 변수인 time_zone 에 설정된 값으로 확인할 수 있습니다.

예시

실제로 데이터가 어떻게 저장되는지 확인해봅시다.

create table date_example
(
  test_date date,
  test_datetime datetime,
  test_timestamp timestamp,
  test_time time
);

insert into date_example values (now(), now(), now(), now());

테스트 테이블을 만들고 현재 시간으로 데이터를 저장했습니다.

mysql> select * from date_example;
+------------+---------------------+---------------------+-----------+
| test_date  | test_datetime       | test_timestamp      | test_time |
+------------+---------------------+---------------------+-----------+
| 2024-07-09 | 2024-07-09 10:36:17 | 2024-07-09 10:36:17 | 10:36:17  |
+------------+---------------------+---------------------+-----------+
1 row in set (0.00 sec)

왼쪽부터 DATE, DATETIME, TIMESTAMP, TIME 형식의 데이터가 저장된 것을 확인할 수 있습니다.

 

여기서 time_zone을 수정해보고 다시 같은 데이터를 확인해보겠습니다.

mysql> SET time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from date_example;
+------------+---------------------+---------------------+-----------+
| test_date  | test_datetime       | test_timestamp      | test_time |
+------------+---------------------+---------------------+-----------+
| 2024-07-09 | 2024-07-09 10:36:17 | 2024-07-09 02:36:17 | 10:36:17  |
+------------+---------------------+---------------------+-----------+
1 row in set (0.00 sec)

time_zone에 변화를 주고 조회해보니 TIMESTAMP 형식의 test_timstamp 값만 변한 것을 알 수 있습니다.

• SET time_zone = '+01:00' : UTC+09:00 보다 8시간 이전으로 설정

이는 위에서 기재했듯 TIMESTAMP 는 시스템 변수인 time_zone을 기준으로 결과를 반환하기 때문입니다.

그렇기때문에 TIMESTAMP를 사용하고, 로컬 시간이 다른 글로벌 서비스를 운영할 때에는 이를 유의해서 관리해야합니다.

 

TIMESTAMP를 사용하면 로컬 시간에 따라 혼란이 있을 수는 있으나, 글로벌 서비스를 운영할때에는 유용하게 쓰일 수 있습니다.

만약 DATETIME을 사용한다면 로컬 시간이 제대로 반영되지 않아서, 서울에서 오전 11시에 작성한 글을 미국에서 조회하는 경우, 동일하게 오전 11시로 반영되는 등의 문제가 발생합니다. 이렇게 시간이 다른 지역이 있는 경우, TIMESTAMP가 더 적합할 수도 있습니다.

 

이러한 특징들로 DATETIME은 절대적인 시간 기록에 적합하고 TIMESTAMP는 시간대에 따라 변환이 필요한 경우 유용하게 쓰입니다.

3) DATETIME 과 TIMESTAMP의 자동 초기화

DATETIME와 TIMESTAMP는 현재 일자와 시간을 기본값, 자동 업데이트 값으로 설정할 수 있습니다.

• 자동 초기화: 데이터 삽입시 명시적으로 지정하지 않아도 현재 타임스탬프로 설정
• 자동 업데이트: 데이터 갱신시 자동으로 현재 타임 스탬프로 갱신

설정 방법

자동 초기화와 업데이트를 설정하려면 DATETIME과 TIMESTAMP 열 정의에서 지정해주면 됩니다.

• 자동 초기화를 위한 초기값 설정: DEFAULT ~~
• 자동 업데이트를 위한 설정: ON UPDATE ~~

DATETIME과 TIMESTAMP를 각각 형식으로 가지는 column이 있는 테이블을 아래와 같이 정의하면 자동으로 값이 들어갑니다.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

위 테이블에 아무 값도 지정하지 않고 INSERT를 하고 조회를 하면, 

insert into t1 values();

아무 값도 넣지 않았는데도 현재 시간이 저장된 것을 확인할 수 있습니다.

mysql> select * from t1;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2024-07-09 12:13:49 | 2024-07-09 12:13:49 |
+---------------------+---------------------+
1 row in set (0.00 sec)

 

이번엔 아래와 같은 테이블을 생성해봅시다.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

위에서 정의한 것과 다른점은 DEFAULT NULL 부분입니다.

이는 기본값을 NULL로 두겠다는 것을 의미합니다.

 

mysql> INSERT INTO t1 VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM T1;
+------+------+
| ts   | dt   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

위와 동일하게 아무 값도 넣지 않고 INSERT를 하면, 이번에는 NULL 값이 저장된 것을 확인할 수 있습니다.

---

마치며,

서비스를 운영하면 필수적으로 포함되는 날짜와 시간 데이터형에 대한 내용을 정리했습니다.

특히 DATETIME과 TIMESTAMP의 차이에서 헷갈리는 부분이 많았는데, 이번에 정리하고 찾아보면서 한번 더 확인할 수 있었던 시간이었습니다. 날짜 데이터와 관련해서는 다음에 문자열로 변환하는 등 실제로 값을 조회하고 처리하는 부분에 대해서 알아보겠습니다.

 

Reference

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

MySQL :: MySQL 8.4 Reference Manual :: 13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME