(MySQL) 시간 관련 데이터 타입

MySQL에서 시간을 나타내는 데이터 타입은 YEAR, DATE, TIME, DATETIME, TIMESTAMP 총 5개가 있다. 아래에서는 각 데이터 타입의 특징에 대해서 정리해 보고자 한다.

 

---

 

1. 시간 데이터 타입 비교

타입	범위	출력 형식
YEAR	1901 ~ 2155	YYYY
DATE	1000-01-01 ~ 9999-12-31	YYYY‑MM‑DD
TIME	-838:59:59 ~ 838:59:59	HHH:MM:SS
DATETIME	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	YYYY‑MM‑DD HH:MM:SS
TIMESTAMP	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC	YYYY‑MM‑DD HH:MM:SS

 

타입	MySQL 5.6.4 이전 저장 공간	MySQL 5.6.4 이후 저장 공간
YEAR	1 byte	1 byte
DATE	3 byte	3 byte
TIME	3 byte	3 byte + fractional seconds storage
DATETIME	8 byte	5 byte + fractional seconds storage
TIMESTAMP	4 byte	4 byte + fractional seconds storage

 

•  fractional seconds storage : 소수점 이하 초를 저장하기 위한 추가 바이트

---

 

2. 각 데이터 타입 별 특징

 

YEAR

• 연도 정보 표시
• 2자리 입력시 자동 변환(70 -> 1970, 23 -> 2023)
  
  

DATE

• 날짜 정보 표시
• 시간 정보 없음
  
  

TIME

• 순수 시각 또는 시간 간격 정보 표시
• 마이크로초 저장 가능 (최대 6자리)
• 시간/간격 혼용 가능
  

CREATE TABLE work_schedule (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  start_time     TIME     NOT NULL,  -- 시각 (근무 시작 시각)
  work_duration  TIME     NOT NULL   -- 경과 시간 (근무 시간)
);

INSERT INTO work_schedule (start_time, work_duration)
VALUES
  ('08:30:00', '08:00:00'),   -- 오전 8시 반, 8시간 근무
  ('13:45:00', '04:30:00'),   -- 오후 1시 45분, 4시간 30분 근무
  ('23:00:00', '02:30:00');   -- 밤 11시, 2시간 30분 야간 근무

 

 

DATETIME

• 날짜 + 시간 정보 표시
• 마이크로초 저장 가능 (최대 6자리)
• 타임존의 영향을 받지 않고 저장된 그대로 출력
• CURRENT_TIMESTAMP를 통한 자동 초기화, 갱신 가능
  

created_at DATETIME
  DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME
  DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP

-- DEFAULT와 ON UPDATE는 다른 데이터 타입에서 사용 가능한 명령어
-- 단, CURRENT_TIMESTAMP 함수는 TIMESTAMP/DATETIME 전용 함수
-- @@session.time_zone에 지정된 타임존(예: 'Asia/Seoul', '+00:00' 등)을 기준으로 시스템 시계를 읽어 해당 타임존의 현재 시각을 계산한 뒤 반환

SET time_zone = 'Asia/Seoul';
SELECT CURRENT_TIMESTAMP;  -- 예: '2025-04-20 14:30:00' (KST)

SET time_zone = '+00:00';
SELECT CURRENT_TIMESTAMP;  -- 예: '2025-04-20 05:30:00' (UTC)

 

 

TIMESTAMP

• 날짜 + 시간 정보 표시 (UTC 기준)
• 마이크로초 저장 가능 (최대 6자리)
• 2038년 이후의 시각은 오버플로우 또는 에러 발생(Y2038 문제) -> 장기 보존 시 DATETIME 권장
• INSERT 시: MySQL 세션(@@session.time_zone)을 기준으로 받은 값을 UTC로 변환해 저장
• SELECT 시: 저장된 UTC 값을 다시 MySQL 세션의 타임존으로 변환해 보여줌

[잘못된 타임존 설정 문제로 발생하는 TIMESTAMP 저장 시나리오]

환경
- 클라이언트(React), 백엔드(Spring Boot): Asia/Seoul(UTC+9)
- MySQL 서버: Asia/Seoul(UTC+9)

시나리오
- React 에서 Spring Boot로 "2025-04-20 12:00:00" 전송
- Spring Boot에서 LocalDateTime -> Timestamp.valueOf() 호출
- JVM 타임존이 서울이므로 UTC+9시간 기준의 “2025-04-20 03:00:00 UTC” 생성 
- MySQL에 “2025-04-20 03:00:00” 값 전달
- 하지만 MySQL의 session 타임존이 서울이므로 “2025-04-20 03:00:00” 을 UTC가 아닌 KST로 해석함
- TIMESTAMP에는 UTC 시간이 들어가야 하므로 “2025-04-20 03:00:00 KST”에서 “2025‑04‑19 18:00:00 UTC” 로 변환되어 저장됨 
- select 쿼리를 통한 데이터 조회 시 db 에 들어가 있는 “2025‑04‑19 18:00:00” 은 UTC 이므로 KST인 “2025-04-20 03:00:00” 으로 조회됨

 

---

 

3. 주로 쓰이는 시간 관련 함수

함수	설명	예시
NOW() / CURRENT_TIMESTAMP()	세션 타임존 기준 “현재 날짜·시간” 반환	SELECT NOW(); -> 2025-04-20 14:30:00
CURDATE() / CURRENT_DATE()	“현재 날짜(YYYY-MM-DD)” 반환	SELECT CURDATE(); -> 2025-04-20
CURTIME() / CURRENT_TIME()	“현재 시간(HH:MM:SS)” 반환	SELECT CURTIME(); -> 14:30:00
DATE_FORMAT(expr, fmt)	expr을 원하는 형식('%Y/%m/%d %H:%i' 등)으로 포맷팅	SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i'); -> 2025/04/20 14:30
DATE_ADD(date, INTERVAL n unit)	date에 n 단위 더하기 (DAY, HOUR 등)	SELECT DATE_ADD('2025-04-20', INTERVAL 7 DAY); -> 2025-04-27
DATE_SUB(date, INTERVAL n unit)	date에서 n 단위 빼기 (DAY, HOUR 등)	SELECT DATE_SUB('2025-04-20', INTERVAL 1 MONTH); -> 2025-03-20
TIMESTAMPDIFF(unit, dt1, dt2)	dt2 – dt1 차이를 unit(SECOND, DAY 등)으로 반환	SELECT TIMESTAMPDIFF(DAY,'2025-04-01','2025-04-20'); -> 19
STR_TO_DATE(str, fmt)	str(문자열)을 fmt 포맷에 맞춰 DATE/DATETIME/TIME 으로 변환	SELECT STR_TO_DATE('31-12-2025','%d-%m-%Y'); -> 2025-12-31
TIME_FORMAT(t, fmt)	t(TIME/DATETIME) 을 fmt 문자열로 포맷팅	SELECT TIME_FORMAT('19:30:10','%h시%i분%s초'); -> 07시30분10초
CAST(expr AS CHAR) / CONVERT(expr, CHAR)	날짜/시간 타입을 문자열(CHAR) 로 변환	SELECT CAST(CURDATE() AS CHAR); -> '2025-04-20'

---

출처 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html https://dev.mysql.com/doc/refman/8.0/en/datetime.html https://dev.mysql.com/doc/refman/8.0/en/time.html https://dev.mysql.com/doc/refman/8.0/en/year.html

---