[DB] MySQL 날짜 더하기/빼기 함수

■ MySQL 날짜 더하기/빼기 함수

단위

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

1. DATE_ADD(DATE, INTERVAL value 단위) / DATE_SUB(DATE, INTERVAL value 단위)

※ 더하기 함수

1) 초(SECOND) 더하기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL 10 SECOND) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL -10 SECOND) FROM DUAL;
: 2020-05-25 10:00:10

2) 분(MINUTE) 더하기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL 10 MINUTE) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL -10 MINUTE) FROM DUAL;
: 2020-05-25 10:10:00

3) 시간(HOUR) 더하기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL 10 HOUR) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL -10 HOUR) FROM DUAL;
: 2020-05-25 20:00:00

4) 일(DAY) 더하기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL 10 DAY) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL -10 DAY) FROM DUAL;
: 2020-06-04 10:00:00

5) 주(WEEK) 더하기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL 10 WEEK) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL -10 WEEK) FROM DUAL;
: 2020-08-03 10:00:00

6) 월(MONTH) 더하기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL 10 MONTH) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL -10 MONTH) FROM DUAL;
: 2021-03-25 10:00:00

7) 연(YEAR) 더하기
=SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL 10 YEAR) FROM DUAL;
=SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL -10 YEAR) FROM DUAL;
: 2030-05-25 10:00:00

※ 빼기 함수

1) 초(SECOND) 빼기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL -10 SECOND) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL 10 SECOND) FROM DUAL;
: 2020-05-25 09:59:50

2) 분(MINUTE) 빼기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL -10 MINUTE) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL 10 MINUTE) FROM DUAL;
: 2020-05-25 09:50:00

3) 시간(HOUR) 빼기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL -10 HOUR) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL 10 HOUR) FROM DUAL;
: 2020-05-25 00:00:00

4) 일(DAY) 빼기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL -10 DAY) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL 10 DAY) FROM DUAL;
: 2020-05-15 10:00:00

5) 주(WEEK) 빼기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL -10 WEEK) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL 10 WEEK) FROM DUAL;
: 2020-03-16 10:00:00

6) 월(MONTH) 빼기
= SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL -10 MONTH) FROM DUAL;
= SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL 10 MONTH) FROM DUAL;
: 2019-07-25 10:00:00

7) 연(YEAR) 빼기
=SELECT DATE_ADD('2020-05-25 10:00:00.0', INTERVAL -10 YEAR) FROM DUAL; 
=SELECT DATE_SUB('2020-05-25 10:00:00.0', INTERVAL 10 YEAR) FROM DUAL;
: 2010-05-25 10:00:00

2. ADDDATE(DATE, INTERVAL value 단위)

※ 더하기 함수

단위

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

1) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 SECOND) FROM DUAL;
: 2020-05-25 10:00:10

2) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 MINUTE) FROM DUAL;
: 2020-05-25 10:10:00

3) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 HOUR) FROM DUAL;
: 2020-05-25 20:00:00

4) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 DAY) FROM DUAL;
: 2020-06-04 10:00:00

5) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 WEEK) FROM DUAL;
: 2020-08-03 10:00:00

6) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 MONTH) FROM DUAL;
: 2021-03-25 10:00:00

7) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 QUARTER) FROM DUAL;
: 2022-11-25 10:00:00

8) SELECT ADDDATE('2020-05-25 10:00:00.0', INTERVAL 10 YEAR) FROM DUAL;
: 2030-05-25 10:00:00