Situations often call for us to calculate the difference between two dates.

An example would be how Facebook store the date and time that a comment is made, and then display how long ago it happened in relation to the current time.

For instance, say I make a comment on a friend's picture on a Monday, then view the picture again on Wednesday, there may be some additional text below the comment that says '2 days ago'.

This is all achieved by comparing two dates. In this particular case, the date that the comment was made and the current date.

Fortunately, SQL has some built-in functions that allow us to compare dates right out of the box.

It's helpful to note that almost any function in SQL that has 'DATE' in its name will also work with times.

To demonstrate how these functions work, we'll use the 'people' data that we created in the original post on dates and times:

firstname | birthdate | birthtime | birthdatetime |
---|---|---|---|

Quentin | 1961-10-08 | 10:06:46 | 1961-10-08 10:06:46 |

Erica | 1984-06-02 | 17:23:07 | 1984-06-02 17:23:07 |

Rebecca | 1976-03-24 | 06:32:27 | 1976-03-24 06:32:27 |

The primary purpose of DATEDIFF() is to calculate the number of days between two dates. Let's use it to figure out how many days ago each person was born.

To do this, we'll need to compare the current date, using *NOW()*, with the values in the *birthdate* column:

```
SELECT firstname, birthdate, DATEDIFF(NOW(), birthdate) FROM people;
Returns:
firstname birthdate DATEDIFF(NOW(), birthdate)
-------------------------------------------------------
Quentin 1961-10-08 21125
Erica 1984-06-02 12852
Rebecca 1976-03-24 15844
```

There we have it! If you wanted to compare two dates in the past you can simply replace NOW() with a date of your choosing.

DATE_ADD() forms the core of date arithmetic in SQL. It uses the following format:

DATE_ADD(*date*, INTERVAL, *expr unit*)

So, it takes in a date (or DATETIME), a number interval and a unit. The INTERVAL keyword simply specifies how many units are to be added.

There are a whole range of units available such as MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK and so on. For a full reference you can refer to the documentation.

To demonstrate, let's use the *birthdatetime* column and add one month to each person's birthday. Using DATE_ADD() we'll need to pass in the column name, as well as the interval of one month:

```
SELECT birthdatetime, DATE_ADD(birthdatetime, INTERVAL 1 MONTH) FROM people;
Returns:
birthdatetime DATE_ADD(birthdatetime, INTERVAL 1 MONTH)
----------------------------------------------------------------
1961-10-08 10:06:46 1961-11-08 10:06:46
1984-06-02 17:23:07 1984-07-02 17:23:07
1976-03-24 06:32:27 1976-04-24 06:32:27
```

As you can see, all of the months have incremented by 1 in the second column.

It's worth noting that if one of the birthdays was in December, DATE_ADD() would automatically increment the year, as well as the month. For example:

```
birthdatetime DATE_ADD(birthdatetime, INTERVAL 1 MONTH)
----------------------------------------------------------------
2040-12-15 08:32:28 2041-01-15 08:32:28
```

You can use DATE_SUB() to do the opposite i.e. subtract one date from another.

Building on what we now know, there are shortcuts for adding and subtracting dates using the '+' and '-' arithmetic operators.

For example, we could achieve the same result as above without DATE_ADD() and with a shorter syntax:

```
SELECT birthdatetime, birthdatetime + INTERVAL 1 MONTH FROM people;
Returns:
birthdatetime birthdatetime + INTERVAL 1 MONTH
----------------------------------------------------------------
1961-10-08 10:06:46 1961-11-08 10:06:46
1984-06-02 17:23:07 1984-07-02 17:23:07
1976-03-24 06:32:27 1976-04-24 06:32:27
```

We could also subtract by two months:

```
SELECT birthdatetime, birthdatetime - INTERVAL 1 MONTH FROM people;
Returns:
birthdatetime birthdatetime - INTERVAL 1 MONTH
----------------------------------------------------------------
1961-10-08 10:06:46 1961-08-08 10:06:46
1984-06-02 17:23:07 1984-04-02 17:23:07
1976-03-24 06:32:27 1976-01-24 06:32:27
```

Ok, that's great. But what if we want to add multiple different units? That's where the '+' and '-' operators come in even more handy.

This time, let's add 3 months and 2 hours to each birthdatetime:

```
SELECT birthdatetime, birthdatetime + INTERVAL 3 MONTH + 2 HOUR FROM people;
Returns:
birthdatetime birthdatetime + INTERVAL 3 MONTH + 2 HOUR
----------------------------------------------------------------
1961-10-08 10:06:46 1962-01-08 12:06:46
1984-06-02 17:23:07 1984-09-02 19:23:07
1976-03-24 06:32:27 1976-06-24 08:32:27
```

The syntax really is that simple.

When working on an application the most common date function you're likely to need is DATEDIFF() in order to work out the difference between two dates.

As you can imagine, this is useful for displaying all kinds of helpful data, including event times, comments, blog posts, birthdays and so on.

When you need to carry out some extra arithmetic, adding and subtracting intervals is pretty darn simple thanks to the arithmetic operators '+' and '-'.