[MySQL] date vs datetime vs timestamp

⏰ date vs datetime vs timestamp

프로젝트 중 스키마 안에 업로드 날짜나 만료 날짜와 같이 날짜와 시간을 나타내는 컬럼을 포함하는 테이블들이 많았다.

 

MySQL 은 날짜와 시간을 나타내는 데이터 타입으로 date 와 datetime 을 사용하지만, 실제 많은 ERD 에서는 timestamp 를 대신 활용하고 있는 것을 본 적이 있었다.

 

차이를 알지 못해서, 구글링을 통해 현재 프로젝트에는 무엇을 적용하는 것이 올바른지 고민해보았다.

 

찾은 내용을 표로 정리해보았다.

 

분류	값 저장 형태	특징	표현 범위
date	YYYY-MM-DD	날짜만 표현 가능, 시간 X	1000-01-01 ~ 9999-12-31
datetime	YYYY-MM-DD hh:mm:ss	날짜와 시간 표현 가능	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	YYYY-MM-DD hh:mm:ss	날짜와 시간 중 작은 범위 표현 가능	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

 

📌 미세한 표현 방식

가장 정확하다는 원자 시계를 생각해보자. 아래는 원자 시계와의 비교로 내 컴퓨터 시간의 정확도를 분석하는 사이트이다.

https://clock.zone/

Exact Time Clock Now (With Seconds, Milliseconds)

앗 그런데, 흔히 관심이 있는 초 뒤에 또 다른 두자리 숫자가 흘러가고 있음을 볼 수 있다. 이는 초의 소수점까지의 시간을 보여주고 있기 때문이다. 초의 소수점 6자리까지의 표현은 (마이크로초) microsecond 를 나타낸다. (백만분의 1 초)

 

datetime 과 timestamp 의 초 표현도 소수점 6자리까지가 가능하다. 그래서 datetime 과 timstamp 타입의 값에는 초 뒤에 오는 소수점과 6자리 내의 숫자를 무시하지 않고 할당할 수 있다.

 

분류	초를 나타내는 형태	범위
datetime	YYYY-MM-DD hh:mm:ss[.fraction]	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
timestamp	YYYY-MM-DD hh:mm:ss[.fraction]	1970-01-01 00:00:00.000000 ~ 2038-01-19 03:24:07.999999

 

💡 datetime 과 timestamp의 차이

datetime과 다르게, timestamp에서 시간을 DB 에 저장할 땐 모두 UTC 시간으로 변환해서 저장한다. 이후 조회할 때는 현재 time zone 의 시간으로 변환해서 조회가 된다. time zone은 서버에서 알아내며, 서버와의 연결 때마다 업데이트된다.

 

이를 이용하면 다음과 같은 기능 구현이 가능하다. 이 것이 timestamp 를 datetime 대신 사용하는 가장 큰 이유이다.

• 하나의 같은 시간을 접속한 지역별로 그 지역의 시간으로 변환해서 보여주는 기능

🔍 etc.

• SQL mode 에 따라, 유효하지 않은 시간을 저장하려고 하면 모든 값은 0으로 초기화되어 저장된다.
• CAST() 쿼리를 사용하면, AT TIME ZONE 연산자를 사용하여 timestamp 값을 특정 time zone 의 datetime 값으로 변환할 수 있다. (예시 1 참고)
• 쿼리로 값을 저장할 때 date 를 저장함에도 구분자로 ' : (colon) ' 를 사용할 수 있다. MySQL 은 문자열로 받아들이기 때문에 구분자에 관대하기 때문이다. 그럴 때는 어떤 데이터 타입의 값을 저장하는지에 유의해야 한다. 구분자로 인해 시간으로 착각해 10:27:12 로 명시한 값이 2010-27-12 로 저장되려 하기 때문에 유효하지 않은 날짜 값이라 결국 0000-00-00 으로 저장되기 때문이다.
• SQL mode 가 strict mode 로 설정되어 있지 않으면 유효하지 않은 날짜와 시간은 모두 0으로 초기화되 저장된다.
• SQL mode 가 strict mode 로 설정되어 있으면 유효하지 않은 날짜와 시간에 대해 에러를 발생시킨다.
• 영리하게도 2022-11-31 (11월은 30일까지다) 은 표현 가능 범위 내에 있지만 유효하지 않은 날짜로 판단된다. 이를 허용하고 싶다면 ALLOW_INVALID_DATES 를 설정해주면 된다.
• 물론 timestamp 는 달과 날에 0을 포함하지 못하게 되어있지만, 0000-00-00 00:00:00 은 가능하다. 물론 SQL mode 중 어떤 strict mode 가 허용되어 있고 NO_ZERO_DATE 가 허용되어 있는지의 여부에 따라 다르다.
• 몇 세기인지 누락하고 년도 값을 저장하면 특정 기준에 따라 20세기인지 21세기인지를 판단한다:
  • 00 ~ 69 : 2000 ~ 2069 로 변환
  • 70 ~ 99 : 1970 ~ 1999 로 변환

mysql> SELECT col,
     >     CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut
     >     FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | ut                  |
+---------------------+---------------------+
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2019-12-31 23:40:10 | 2020-01-01 04:40:10 |
| 2020-01-01 13:10:10 | 2020-01-01 18:10:10 |
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2020-01-01 04:40:10 | 2020-01-01 09:40:10 |
| 2020-01-01 18:10:10 | 2020-01-01 23:10:10 |
+---------------------+---------------------+

31

예시 1. CAST 와 AT TIME ZONE 사용 방법. INTERVAL '+00:00' 을 사용해 UTC 시간으로 변환했다.

 

출처

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

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