MySQL 날짜 더하기, 뺴기, 차이

---

NOW - 현재 시간

SELECT NOW() FROM dual;

DATEDIFF - 날짜 차이 (일 차이)

SELECT DATEDIFF('2019-12-07 15:15:30','2019-11-07 15:15:30') FROM dual;
//30

TIMESTAMPDIFF - 날짜 차이

• 초    - SECOND

• 분    - MINUTE

• 시    - HOUR

• 일    - DAY

• 주    - WEEK

• 월    - MONTH

• 분기 - QUARTER

• 연    - YEAR

SELECT TIMESTAMPDIFF(YEAR ,'2018-12-07', '2019-12-07') FROM dual;
//1
SELECT TIMESTAMPDIFF(SECOND ,'2018-12-07', '2019-12-08') FROM dual;
//31622400

DATE_SUB 날짜 빼기

• 1초 뺴기 - INTERVAL 1 SECOND

• 1분 뺴기 - INTERVAL 1 MINUTE

• 1시간 뺴기 - INTERVAL 1 HOUR

• 1일 뺴기 - INTERVAL 1 DAY

• 1달 뺴기 - INTERVAL 1 MONTH

• 1년 뺴기 - INTERVAL 1 YEAR

SELECT DATE_SUB('2019-12-10 17:00:00', INTERVAL 1 SECOND) FROM dual;
//2019-12-10 16:59:59
SELECT DATE_SUB('2019-12-10 17:00:00', INTERVAL 1 MINUTE) FROM dual;
//2019-12-10 16:59:00
SELECT DATE_SUB('2019-12-10 17:00:00', INTERVAL 1 HOUR) FROM dual;
//2019-12-10 16:00:00
SELECT DATE_SUB('2019-12-10 17:00:00', INTERVAL 1 DAY) FROM dual;
//2019-12-09 17:00:00
SELECT DATE_SUB('2019-12-10 17:00:00', INTERVAL 1 MONTH) FROM dual;
//2019-11-10 17:00:00
SELECT DATE_SUB('2019-12-10 17:00:00', INTERVAL 1 YEAR) FROM dual;
//2018-12-10 17:00:00

DATE_ADD 날짜 더하기

• 1초 더하기 - INTERVAL 1 SECOND

• 1분 더하기 - INTERVAL 1 MINUTE

• 1시간 더하기 - INTERVAL 1 HOUR

• 1일 더하기 - INTERVAL 1 DAY

• 1달 더하기 - INTERVAL 1 MONTH

• 1년 더하기 - INTERVAL 1 YEAR

SELECT DATE_ADD('2019-12-10 17:00:00', INTERVAL 1 SECOND) FROM dual;
//2019-12-10 17:00:01
SELECT DATE_ADD('2019-12-10 17:00:00', INTERVAL 1 MINUTE) FROM dual;
//2019-12-10 17:01:00
SELECT DATE_ADD('2019-12-10 17:00:00', INTERVAL 1 HOUR) FROM dual;
//2019-12-10 18:00:00
SELECT DATE_ADD('2019-12-10 17:00:00', INTERVAL 1 DAY) FROM dual;
//2019-12-11 17:00:00
SELECT DATE_ADD('2019-12-10 17:00:00', INTERVAL 1 MONTH) FROM dual;
//2020-01-10 17:00:00
SELECT DATE_ADD('2019-12-10 17:00:00', INTERVAL 1 YEAR) FROM dual;
//2020-12-10 17:00:00