SQL 날짜 계산 관련 함수들 (날짜 차이 등)

SQL 시간 계산 함수들

MySQL을 사용하다보니, 일자와 시간차이 계산이 필요한 경우가 많다.
자주 사용하는 것들을 정리해봤다.

 

 

들어가기에 앞서

• MySQL은 UTC를 기준으로 시간 출력
• 대한민국 시간대 KST는 UTC+9
• UTC 0인 영국 런던시간이 0시일 때, 한국은 9시간 빠른 오전 09시다
• 따라서 MySQL에 시간 +,- 필요

 

 

1) 현재 날짜를 구하는 함수

함수명	역할
current_date()	yyyy-mm-dd 형식 출력
now()	yyyy-mm-dd hh:mm:ss 형식 출력
current_time()	hh:mm:ss 형식 출력
current_timestamp()	yyyy-mm-dd hh:mm:ss 형식 출력

 

# 예)
select current_date(); #2022-05-13
select now(); #2022-05-13 07:17:25
select current_time(); #07:17:25
select current_timestamp(); #2022-05-13 07:17:25

 

---

2-1) now() 와 current_timestamp() 차이

• now() 와 current_timestamp()의 출력형식이 같다.
• current_timestamp()는 time_zone의 영향을 받는다.
• 따라서 time_zone이 변경되면 current_timestamp()는 time_zone에 따른 시간을 반환한다.

---

2-2) MySQL time_zone 확인

• show variables like '%time_zone%';

---

2-3) utc를 지원하는 timestamp()를 권장

여러나라에 서비스하게 될 경우, now()로 시간을 계산하면, 시차가 반영되지 않는다.
예를 들면, 서울 오전 7시에 예약을 신청하면, 미국에서 확인했을 때도 예약 당시 시간이 오전 7시로 찍힌다.
서울과 미국의 시차를 전혀 고려하지 못하는 것!
때문에 utc를 지원하는 timestamp()함수를 권장한다.

---

2. 연,월,일 더하기 빼기

1) MySQL서버일자로 현재 일자 가져오는 함수(연-월-일 시:분:초) : select now()

2) DATE_ADD : 기준 날짜에 입력된 기간 만큼 더하기
<형식> DATE_ADD(기준날짜, INTERVAL 계산수 계산타입);
※뺄 때 DATE_SUB 대신에, DATE_ADD에 계산수를 -로 표현해도 된다.

3) DATE_SUB : 기준 날짜에 입력된 기간만큼 빼기
<형식> DATE_SUB(기준날짜, INTERVAL 계산수 계산타입);

4) 계산 타입(unit) : SECOND, MINUTE, HOUR, DAY, MONTH

# 예) 현재 시간에 1일 더하기
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

# 예) 현재 시간에 한 달 더하기
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

6) TIMESTAMPADD(unit, interval, datetime_expr)
(1) unit에 연YEAR, 분기QUARTER, 월MONTH, 주WEEK, 일DAY, 시HOUR, 분MINUTE, 초SECOND 단위 대입해서 두 날짜 차이를 출력형식 지정

# 예)
SELECT TIMESTAMPADD(WEEK, 1, '2022-05-15'); #2022-05-22
SELECT TIMESTAMPADD(WEEK, 1, '2022-05-15 19:15:00'); #2022-05-22 19:15:00

6-2) timestampadd(unit, ‘일자1’, '일자2');
일자1에서 unit이 얼마나 지나야 일자2인지 출력
일자1이 작은날짜여야하고, 일자2가 다음날짜여야 양수값 출력

 

---

3. 상세하게 일자 계산하기

함수명	역할
DATEDIFF(expr, expr2)	두 기간 사이의 일수 계산
TIMEDIFF(expr, expr2)	두 기간 사이의 시간 계산
PERIOD_DIFF(expr, expr2)	두 기간 사이의 개월 수 계산
TIMESTAMPDIFF(expr, expr2)	두 기간 사이의 시간 계산

※expr은 시작일자, expr2는 종료일자

1) 두 날짜 차이 계산 함수 (날짜 차이를 일자로 반환)
DATEDIFF(expr, expr2)
(1) expr – expr2 로 날짜 차이를 일자로 반환한다.
(2) expr2가 더 나중 일자라면, 값이 –로 반환된다.

# 날짜차이 계산 예시
select now(); #2022-05-13 07:14:35
select current_date(); #2022-05-13
SELECT DATEDIFF('2022-12-31','2022-12-25'); #결과 6
SELECT DATEDIFF('2022-12-31', CURRENT_DATE()); #232

---

2) TIMEDIFF(expr, expr2)
expr – expr2을 시간으로 반환한다.
expr에 나중 시간을 입력해야 양수로 반환된다.

# 예)
select timediff('2022-05-15 16:33:00','2022-05-15 22:35:00'); #-06:02:00

※ 2022-05-15 16:33:00 대신에 current_timestamp() 같은 날짜 함수 사용 가능

---

 

3) 두 날짜 차이를 반환 형식 지정해서 가져오는 함수
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
(1) unit에 연YEAR, 분기QUARTER, 월MONTH, 주WEEK, 일DAY, 시HOUR, 분MINUTE, 초SECOND 단위 대입해서 두 날짜 차이를 출력형식 지정
(2) datetime_expr1는 시작일, datetime_expr2는 종료일

SELECT TIMESTAMPDIFF(hour,'2022-05-15','2022-12-30'); #5496
SELECT TIMESTAMPDIFF(day,'2022-05-15','2022-12-30'); #229
SELECT TIMESTAMPDIFF(month,'2022-05-15','2022-12-30'); #7
SELECT TIMESTAMPDIFF(quarter,'2022-05-15','2022-12-30'); #2

 

써놓고 보니 예제 대부분의 날짜가 스승의 날이네요. 쌤들 잘계시죠?

---

SQL 날짜차이 계산 함수 요약 정리

#날짜 반환 함수
#1. 현재일자 출력
select current_date(); #2022-05-15
select now(); #2022-05-15 07:17:25
select current_time(); #07:17:25
select current_timestamp(); #2022-05-15 07:17:25 => time_zone이 바뀌면 변경된다.
show variables like '%time_zone%'; #mysql 타임존 조회

#2. 일자 차이 출력
#1) 일자로 출력
SELECT DATEDIFF('2022-12-31', '2022-12-25'); #6
SELECT DATEDIFF('2022-12-31', CURRENT_DATE()); #230

#2) 시간으로 출력
select timediff('2022-05-15 16:33:00','2022-05-15 22:35:00'); #-06:02:00

#3) 반환할 형식을 지정해서 차이 출력
SELECT TIMESTAMPDIFF(hour,'2022-05-15','2022-12-30'); #5496
SELECT TIMESTAMPDIFF(day,'2022-05-15','2022-12-30'); #229
SELECT TIMESTAMPDIFF(month,'2022-05-15','2022-12-30'); #7
SELECT TIMESTAMPDIFF(quarter,'2022-05-15','2022-12-30'); #2

#4) 날짜 더하기 빼기
SELECT TIMESTAMPADD(WEEK, 1, '2022-05-15'); #2022-05-22
SELECT TIMESTAMPADD(WEEK, 1, '2022-05-15 19:15:00'); #2022-05-22 19:15:00

 

여기에 작성한 것 말고도 엄청나게 많은 함수들이 있습니다. 정말 많아요...하하핳

필요에따라 사용하시면 되겠습니다.

그럼 이만.

 

 

 

 

 

참고사이트

MySQL 8.0 Reference Manual
포뇨아빠
日日新又日新
Nesoy Blog
yeon.Biju