Mysql/Mariadb DATE_ADD/ADDDATE/DATE_SUB (날짜/시간 더하기, 빼기 )

date 함수를 이용하여 시간을 더하거나 빼기를 알아보겠습니다.

​

interval : seconde(초)

minute(분)

hour(시간)

day(일)

month(월)

year(년)

​

1. 날짜/시간 더하기

함수 사용법 : DATE_ADD(기준시간, interval ) 또는 adddate(기준시간, interval )

기준시간을 기준으로 interval만큼의 시간을 더한다.

만약 interver에 마이너스(-)를 입력하면 시간을 뺀다.

-- 현재시간을 기준으로 조회
mysql> select now() now, date_add(now() , interval 1 second) sec, 
              date_add(now() , interval 1 minute) min, date_add(now() , interval 1 hour) hour,
              date_add(now() , interval 1 day) day, date_add(now() , interval 1 month) mon, 
              date_add(now() , interval 1 year) year;
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| now                 | sec                 | min                 | hour                | day                 | mon                 | year                |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2020-11-16 08:52:01 | 2020-11-16 08:52:02 | 2020-11-16 08:53:01 | 2020-11-16 09:52:01 | 2020-11-17 08:52:01 | 2020-12-16 08:52:01 | 2021-11-16 08:52:01 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

-- 날짜를 지정하여 조회
mysql> select '2020-01-01' now, date_add('2020-01-01', interval 1 second) sec, 
              date_add('2020-01-01', interval 1 minute) min, date_add('2020-01-01', interval 1 hour) hour,
              date_add('2020-01-01', interval 1 day) day, date_add('2020-01-01', interval 1 month) mon, 
              date_add('2020-01-01', interval 1 year) year;
+------------+---------------------+---------------------+---------------------+------------+------------+------------+
| now        | sec                 | min                 | hour                | day        | mon        | year       |
+------------+---------------------+---------------------+---------------------+------------+------------+------------+
| 2020-01-01 | 2020-01-01 00:00:01 | 2020-01-01 00:01:00 | 2020-01-01 01:00:00 | 2020-01-02 | 2020-02-01 | 2021-01-01 |
+------------+---------------------+---------------------+---------------------+------------+------------+------------+

2. 날짜/시간 빼기

함수 사용법 : DATE_SUB(기준시간, interval )

기준시간을 기준으로 interval만큼의 시간을 뺀다.

-- 현재시간을 기준으로 조회
mysql> select now() now, date_sub(now(), interval 1 second) sec, 
              date_sub(now(), interval 1 minute) min, date_sub(now(), interval 1 hour) hour,
              date_sub(now(), interval 1 day) day, date_sub(now(), interval 1 month) mon, 
              date_sub(now(), interval 1 year) year;
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| now                 | sec                 | min                 | hour                | day                 | mon                 | year                |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2020-11-16 08:44:07 | 2020-11-16 08:44:06 | 2020-11-16 08:43:07 | 2020-11-16 07:44:07 | 2020-11-15 08:44:07 | 2020-10-16 08:44:07 | 2019-11-16 08:44:07 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

-- 날짜를 지정하여 조회
mysql> select '2020-01-01' now, date_sub('2020-01-01', interval 1 second) sec, 
              date_sub('2020-01-01', interval 1 minute) min, date_sub('2020-01-01', interval 1 hour) hour,
              date_sub('2020-01-01', interval 1 day) day, date_sub('2020-01-01', interval 1 month) mon, 
              date_sub('2020-01-01', interval 1 year) year;
+------------+---------------------+---------------------+---------------------+------------+------------+------------+
| now        | sec                 | min                 | hour                | day        | mon        | year       |
+------------+---------------------+---------------------+---------------------+------------+------------+------------+
| 2020-01-01 | 2019-12-31 23:59:59 | 2019-12-31 23:59:00 | 2019-12-31 23:00:00 | 2019-12-31 | 2019-12-01 | 2019-01-01 |
+------------+---------------------+---------------------+---------------------+------------+------------+------------+

이상 Mysql/MariaDB 날짜/시간 더하기/빼기에 대해 설명하였습니다.