MySQL DATE_ADD(), DATE_SUB()

MySQL 날짜함수, 시간함수

 

DATE_ADD(), DATE_SUB()

   - 날짜 더하기 , 빼기

 

DATE_ADD(date, INTERVAL expr unit)

DATE_SUB(date, INTERVAL expr unit)

의 형태

 

These functions perform date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added or substracted from the starting date. expr is evaluated as a string; It amy start with a - for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. 

 

The return value depends on the arguments:

• DATE if the date argument is a DATE value and your calculations involve only YEAR, MONTH and DAY parts(that is, no time parts).
• DATETIME if the first argument is a DATETIME(or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOUR, MINUTES, or SECONDS.
• String otherwise.

To ensure that the result DATETIME, you can use CAST() to convert the first argument to DATETIME.

 

 

SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND); 
SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE); 
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); 
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); 
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH); 
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

 

아래와 같이 사용도 가능하기 때문에 DATA_SUB() 기능도 할 수 있다.

 

SELECT DATE_ADD(NOW(), INTERVAL -1 SECOND);  
SELECT DATE_ADD(NOW(), INTERVAL -1 MINUTE);  
SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR);  
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);  
SELECT DATE_ADD(NOW(), INTERVAL -1 MONTH);  
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);

 

 

SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND); 
SELECT DATE_SUB(NOW(), INTERVAL 1 MINUTE); 
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR); 
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); 
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); 
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);

 

아래와 같이 사용도 가능하기 때문에 DATE_ADD 기능도 할 수 있다. 

 

SELECT DATE_SUB(NOW(), INTERVAL -1 SECOND);  
SELECT DATE_SUB(NOW(), INTERVAL -1 MINUTE);  
SELECT DATE_SUB(NOW(), INTERVAL -1 HOUR);  
SELECT DATE_SUB(NOW(), INTERVAL -1 DAY);  
SELECT DATE_SUB(NOW(), INTERVAL -1 MONTH);  
SELECT DATE_SUB(NOW(), INTERVAL -1 YEAR); 

 

mysql> SELECT DATE_ADD('2020-01-01', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2020-01-01', INTERVAL 1 DAY) |
+----------------------------------------+
| 2020-01-02                             |
+----------------------------------------+
1 row in set (0.00 sec)

 

mysql> SELECT DATE_ADD('1999-12-31 23:59:59.0000002', INTERVAL '1.999999' SECOND_MICROSECOND);
+---------------------------------------------------------------------------------+
| DATE_ADD('1999-12-31 23:59:59.0000002', INTERVAL '1.999999' SECOND_MICROSECOND) |
+---------------------------------------------------------------------------------+
| 2000-01-01 00:00:00.999999                                                      |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

 

INTERVAL unit 으로 쓸 수 있는 것들

unit value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'