How to add and subtract dates and times in MySQL

Reference

DATE_ADD and DATE_SUB

These two functions are a pair, one is addition and the other is subtraction. Their function is to add or subtract a period of time from the specified date. For specific usage, refer to the following example. In the first example, we add one month to the current time, and the second one subtracts one month from the current time.

            SELECT DATE_ADD(now(), INTERVAL 1 MONTH);
SELECT DATE_SUB(now(), INTERVAL 1 MONTH);
        

ADDDATE and SUBDATE

These two functions are also a pair, one is addition and the other is subtraction. Their usage is the same as DATE_ADD and DATE_SUB. For specific usage, refer to the following example. In the first example, we add one month to the current time, and the second one subtracts one month from the current time.

            SELECT ADDDATE(now(), INTERVAL 1 MONTH);
SELECT SUBDATE(now(), INTERVAL 1 MONTH);
        

More usage

We only use month as a parameter in the example, however, hour, minute, second, week, day, year can also be used as parameters, and can also support minutes and seconds, hours and minutes and seconds and more combination. For details, please refer to the list below.

            MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
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'
        

other information

In fact, subtraction can also use DATE_ADD, we only need to write the parameter as a negative number. In addition, MariaDB is a branch of MySQL, and their SQL syntax is basically the same, so there are the above four functions on MariaDB, and the usage is exactly the same.