# MySQL Date and Time Arithmetic

## Date and Time in MySQL – Part 8

**Foreword: In this part of the series I explain functions used to add and subtract date and time in MySQL.**

By: Chrysanthus Date Published: 13 Apr 2016

### 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.

**The SUBDATE() Function**

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

### Related Links

Implementing Database in MySQLProgramming in MySQL

Backup Basics in MySQL

MySQL Access Privileges

Regular Expressions in MySQL

Date and Time in MySQL

Event in MySQL

MySQL Transaction

PurePerl MySQL API Prepared Statements

**More Related Links**

PurePerl MySQL Command Line Tool

Major in Website Design

Perl Course - Optimized

Web Development Course

**BACK**

**NEXT**