MySQL Date and Time Arithmetic
Date and Time in MySQL – Part 8
Forward: In this part of the series I explain functions used to add and subtract date and time in MySQL.
By: Chrysanthus Date Published: 4 Sep 2012
Introduction
The ADDDATE() Function
You can add an interval to a date. You do this with the ADDDATE() Function. The syntax is:
ADDDATE(date,INTERVAL expr unit)
The following statement adds a number of days to a date:
SELECT adddate('2010-01-02', INTERVAL 25 DAY);
Note: in the statement, after the reserved word, INTERVAL, you have the figure for the interval and then the unit for the interval. There are two arguments in the parentheses of the function. The return value for this statement is, 2010-01-27.
The ADDTIME() Function
You can add an interval to a time or datetime value. The ADDTIME() Function does the trick. The syntax is:
ADDTIME(expr1,expr2)
The following two statements illustrate this:
SELECT addtime('2007-12-31 23:59:59', '1 1:1:1');
SELECT addtime('01:00:00', '02:00:00');
The output of the first statement is: 2008-01-02 01:01:00 . The output of the second statement is 03:00:00 . Note: with the ADDTIME function, the interval does not need to have units. As I said in the previous part of the series, do not worry about the mechanics of the addition. Just know that if you type the statement respecting the rules, MySQL will do the addition correctly. This advice goes for all the arithmetic functions in this series.
You can subtract an interval from a date. You do this with the SUBDATE() Function. The syntax is:
SUBDATE(date,INTERVAL expr unit)
The following statement subtracts a number of days from a date:
SELECT subdate('2009-01-02', INTERVAL 31 DAY);
The output (return) value for this statement is, 2008-12-02
The SUBTIME() Function
You can subtract an interval from a time or datetime value. The SUBTIME() Function does the trick. The syntax is:
SUBTIME(expr1,expr2)
The following two statements illustrate this:
SELECT subtime('2007-12-31 23:59:59','1 1:1:1');
SELECT subtime('02:00:00', '01:00:00');
The output of the first statement is: 2007-12-30 22:58:58 . The output of the second statement is 01:00:00 . Note: with the SUBTIME function, the interval does not need to have units.
Adding and Subtracting Interval without Function
In the previous part of the series, I explained how the interval can be added using the addition sign or subtracted using the subtraction sign. I suggest you go to the end of the previous part of the series and see the examples if you have not already done so.
That is it for this part of the series. We stop here and continue in the next part.
Chrys