[MYSQL] DATEDIFF, TIMESTAMPDIFF

MySQL에서 두 날짜간의 차이를 가져올 때 사용하는 함수 2개가 있습니다.

• DATEDIFF : 단순히 일 차이를 가져올 때 사용하는 함수
• TIMESTAMPDIFF: 연, 분기, 월, 주, 일, 시, 분, 초를 지정하여 가져올 때 사용하는 함수

---

사용법

DATEDIFF

DATEDIFF(날짜1, 날짜2);

(날짜1 - 날짜2) 연산이 됩니다.

 

TIMESTAMPDIFF

TIMESTAMPDIFF(단위, 날짜1, 날짜2);

 

단위

• SECOND: 초
• MINUTE: 분
• HOUR: 시
• DAY: 일
• WEEK: 주
• MONTH: 월
• QUARTER: 분기
• YEAR: 연

---

예제

DATEDIFF 예제 1

SELECT DATEDIFF("2023-05-06", "2023-07-15");

result: 70

 

 

DATEDIFF 예제 2

SELECT DATEDIFF("2023-05-06 23:50:50", "2023-07-15 00:00:00");

reseult: 70

---

TIMESTAMPDIFF: 초

SELECT TIMESTAMPDIFF(SECOND, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(SECOND, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

 

result: 6048000

 

 

TIMESTAMPDIFF: 분

SELECT TIMESTAMPDIFF(MINUTE, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(MINUTE, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

result: 100800

 

 

TIMESTAMPDIFF: 시

SELECT TIMESTAMPDIFF(HOUR, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(HOUR, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

result: 1680

 

 

TIMESTAMPDIFF: 일

SELECT TIMESTAMPDIFF(DAY, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(DAY, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

result: 70

 

 

TIMESTAMPDIFF: 주

SELECT TIMESTAMPDIFF(WEEK, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(WEEK, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

result: 10

 

 

TIMESTAMPDIFF: 월

SELECT TIMESTAMPDIFF(MONTH, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(MONTH, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

result: 2

 

 

TIMESTAMPDIFF: 분기

SELECT TIMESTAMPDIFF(QUARTER, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(QUARTER, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

result: 0

 

 

TIMESTAMPDIFF: 연

SELECT TIMESTAMPDIFF(YEAR, "2023-05-06", "2023-07-15");
-- SELECT TIMESTAMPDIFF(YEAR, "2023-05-06 00:00:00", "2023-07-15 00:00:00");

result: 0