[MySQL] DATE 자료형 정리

MySQL의 날짜 시간 타입

Mysql에는 시간에 대한 다양한 자료형이 있다. 대표적으로 TIMESTAMP , DATE , DATETIME 같은 것들이 있다.

JAVA를 주로 사용하는 나를 기준으로 하자면 LocadDate -> DATE, LocalDateTime -> DATETIME 로 바뀌게 된다. 그렇다면 각자 무엇을 어떻게 나타내주는 것인지 알아보자.

---

DATE 와 DATETIME

주로 사용되는 자료형이다. 둘 다 타입명 자체에서 알 수 있듯이 날짜와 날짜+시간을 저장하기 위한 타입이다.

DATE : 'YYYY-MM-DD' 포맷으로 표현되고, DATETIME : 'YYYY-MM-DD hh:mm:ss' 의 포맷으로 표현된다.

이 때 구분자는 다른 구두점으로도 변경 가능하나 주의를 하며 변경해야한다.

DATE의 범위는 '1000-01-01' ~ '9999-12-31' 이고, DATETIME 의 범위는 '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59' 이다.

이 범위를 벗어나는 날짜에 대한 표현을 하고 싶다면 다양한 방식을 사용할 수 있다.

1. VARCHAR or CHAR 타입 사용

CREATE TABLE historical_dates (
    date_str CHAR(20) -- 문자열로 날짜를 저장
);

INSERT INTO historical_dates (date_str) VALUES ('-0500-01-01'); -- 기원전 500년
INSERT INTO historical_dates (date_str) VALUES ('1500-01-01');  -- 기원후 1500년

다음과 같이 표현하게 되면 저장하거나 관리하기는 편하지만 연산 자체에 어려움이 생길 수 있다.

1. 숫자 + 날짜를 활용한 2개의 컬럼으로 저장하기

CREATE TABLE historical_dates (
    year INT,       -- 연도 부분 (기원전은 음수로 표현)
    date DATE       -- 날짜 부분
);

INSERT INTO historical_dates (year, date) VALUES (-500, '0000-01-01'); -- 기원전 500년
INSERT INTO historical_dates (year, date) VALUES (2023, '2023-11-20'); -- 기원후 2023년

연도에 대한 처리만 해줄 수 있다면 처리가 쉬워진다. 다만 헷갈리기 쉽기 때문에 선호하는 방식은 아니다.

1. Julian Day Number 사용

CREATE TABLE historical_dates (
    julian_day BIGINT -- Julian Day 값
);

INSERT INTO historical_dates (julian_day) VALUES (-2100000); -- 과거 날짜
INSERT INTO historical_dates (julian_day) VALUES (2451545);  -- 현대 날짜

기원전 4713년을 기준으로 하는 날짜 계산법을 통해 사용이 가능하다. 다만, 사람이 읽기 어렵고 계산을 위해 별도 처리를 해주어야 하기 때문에 사용이 어렵다.

---

DATETIME 와 TIMESTAMP

둘 다 날짜 + 시간을 표현하기 위한 타입이다. 하지만 왜 두개가 있을까?

차이점

• 표현 가능한 범위의 차이가 존재한다.
  • 위에서 본 것처럼 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 에 대한 표현이 가능하다.
• 데이터 저장 방식에 대한 차이가 존재한다.
  • DATETIME 는 커넥션의 시간대 정보를 그대로 저장하며, 조회 시에도 그대로 반환한다.
  • TIMESTAMP 는 커넥션의 시간대 정보를 바탕으로 UTC로 저장하며, 조회 시에도 UTC로 저장된 정보를 바탕으로 커넥션의 시간대에 따라 변환해서 반환한다. 즉 하나의 공통된 기준을 가지고 시간을 잡는 것이다.

정리하면 다음 표와 같다.

특징	DATETIME	TIMESTAMP
시간대 처리	시간대 정보 없이 그대로 저장	UTC로 변환 후 저장, 조회 시 변환 가능
저장 범위	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 (2038년 문제 존재)
저장 시 크기	8바이트	4바이트
변환 및 동기화 용이성	시간대 고려 없이 단순 저장	시간대를 고려해 변환 가능, 글로벌 동기화 적합
적합한 용도	시간대 민감하지 않은 데이터 (예: 생일)	시간대 민감한 데이터 (예: 로그 타임스탬프)

하지만 위에서 살펴본 바로는 LocalDateTime를 쓰면 DATETIME로 저장된다고 했다. 그럼 시간대를 포함하여 정보를 저장하고 싶으면 어떻게 해야할까? 아래 표를 참고하자.

---

Hibernate와 JPA의 Java 8 날짜 및 시간 API 지원

Hibernate와 JPA는 Java 8의 날짜 및 시간 API를 지원하므로, MySQL의 시간 타입과 Java의 시간 클래스는 아래와 같이 매핑됩니다:

Java 시간 타입	MySQL 데이터 타입	설명
LocalDate	DATE	날짜만 저장 (시간 정보 없음).
LocalDateTime	DATETIME	날짜와 시간을 저장 (시간대 정보 없음).
ZonedDateTime	DATETIME	시간대 정보 포함, 하지만 MySQL에는 시간대 정보가 저장되지 않음 (변환 시 사용).
OffsetDateTime	TIMESTAMP	시간대와 UTC 변환을 지원.
Instant	TIMESTAMP	UTC 기준으로 시간 저장, 시간대 변환은 애플리케이션에서 처리.

---

유효하지 않은 값을 처리하는 방식

날짜를 처리하다보면 유효하지 않은 날짜나 자료형 특성상 저장하지 못하는 값이 있을 수 있다.

• SQL mode의 ZERO_DATE 를 허용하면 틀린 날짜에 대해 혹은 본인의 입력에 의해 0000-00-00 , 0000-00-00 00:00:00 로 저장될 수 있다.
• ALLOW_INVALID_DATE 옵션이 활성화 되어있다면 틀린 날짜에 대해 0000-00-00 , 0000-00-00 00:00:00 로 저장될 수 있다.