3.3.4.5 Date Calculations

MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.

To determine how many years old each of your pets is, compute the difference in the year part of the current date and the birth date, then subtract one if the current date occurs earlier in the calendar year than the birth date. The following query shows, for each pet, the birth date, the current date, and the age in years.

mysql> SELECT name, birth, CURRENT_DATE,
    -> (YEAR(CURRENT_DATE)-YEAR(birth))
    -> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet;
+----------+------------+--------------+------+
| name     | birth      | CURRENT_DATE | age  |
+----------+------------+--------------+------+
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
| Fang     | 1990-08-27 | 2001-08-29   |   11 |
| Bowser   | 1989-08-31 | 2001-08-29   |   11 |
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
+----------+------------+--------------+------+

Here, YEAR() pulls out the year part of a date and RIGHT() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date. The part of the expression that compares the MM-DD values evaluates to 1 or 0, which adjusts the year difference down a year if CURRENT_DATE occurs earlier in the year than birth. The full expression is somewhat ungainly, so an alias (age) is used to make the output column label more meaningful.

The query works, but the result could be scanned more easily if the rows were presented in some order. This can be done by adding an ORDER BY name clause to sort the output by name:

mysql> SELECT name, birth, CURRENT_DATE,
    -> (YEAR(CURRENT_DATE)-YEAR(birth))
    -> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY name;
+----------+------------+--------------+------+
| name     | birth      | CURRENT_DATE | age  |
+----------+------------+--------------+------+
| Bowser   | 1989-08-31 | 2001-08-29   |   11 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Fang     | 1990-08-27 | 2001-08-29   |   11 |
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
+----------+------------+--------------+------+

To sort the output by age rather than name, just use a different ORDER BY clause:

mysql> SELECT name, birth, CURRENT_DATE,
    -> (YEAR(CURRENT_DATE)-YEAR(birth))
    -> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY age;
+----------+------------+--------------+------+
| name     | birth      | CURRENT_DATE | age  |
+----------+------------+--------------+------+
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Fang     | 1990-08-27 | 2001-08-29   |   11 |
| Bowser   | 1989-08-31 | 2001-08-29   |   11 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
+----------+------------+--------------+------+

A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is NULL. Then, for those with non-NULL values, compute the difference between the death and birth values:

mysql> SELECT name, birth, death,
    -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

The query uses death IS NOT NULL rather than death <> NULL because NULL is a special value. This is explained later. See section 3.3.4.6 Working with NULL Values.

What if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides several date-part extraction functions, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and MONTH(birth):

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

Finding animals with birthdays in the upcoming month is easy, too. Suppose the current month is April. Then the month value is 4 and you look for animals born in May (month 5) like this:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

There is a small complication if the current month is December, of course. You don't just add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1).

You can even write the query so that it works no matter what the current month is. That way you don't have to use a particular month number in the query. DATE_ADD() allows you to add a time interval to a given date. If you add a month to the value of CURRENT_DATE, then extract the month part with MONTH(), the result produces the month in which to look for birthdays:

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH));

A different way to accomplish the same task is to add 1 to get the next month after the current one (after using the modulo function (MOD) to wrap around the month value to 0 if it is currently 12):

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURRENT_DATE), 12) + 1;

Note that MONTH returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

User Comments

In a business context, a more interesting query
for this sample db might be the one alluded to
earlier in the tutorial - select rows whose
birthdays are coming up soon to send out a
reminder...

Here is the way I did that:

SET @bdayThreshhold=150;

SELECT name, birth, CONCAT(((RIGHT(birth,5) <
RIGHT(CURRENT_DATE,5))
+ YEAR(CURRENT_DATE)), RIGHT(birth,6)) AS bday,
TO_DAYS(CONCAT(((RIGHT(birth,5) < RIGHT
(CURRENT_DATE,5))
+ YEAR(CURRENT_DATE)), RIGHT(birth,6))) - TO_DAYS
(CURRENT_DATE) AS toBday
FROM pet
WHERE (TO_DAYS(CONCAT(((RIGHT(birth,5) < RIGHT
(CURRENT_DATE,5))
+ YEAR(CURRENT_DATE)), RIGHT(birth,6))) - TO_DAYS
(CURRENT_DATE) < @bdayThreshhold)
ORDER BY bday, RIGHT(birth,5);

Note that I set the threshhold kind of high in
order to make sure that I matched something in
the limited tutorial dataset. Normally you would
probably want 15-30 days.

Here is another useful way of calculating age that I
have used in some of my own work: SELECT DATE
FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS
(dob)), '%Y')+0 AS age FROM people;

RE:
How can I find next Friday's date?

SELECT DATE_ADD(CURRENT_DATE,INTERVAL (6-DAYOFWEEK(CURRENT_DATE)) DAY);

How can I find this month, second Wednesday's date?

SET @First = CONCAT(EXTRACT(YEAR_MONTH FROM (DATE_ADD(CURRENT_DATE,INTERVAL 1 MONTH))),"01");
SELECT DATE_ADD(@First,INTERVAL (11 + (7 * (DAYOFWEEK(@First) > 4)) - DAYOFWEEK(@First)) DAY);

Hope this helps, no matter how tardy it is.

-Bryan

If you need to know who's birthday is within the next X days, you can use something like This:

select * from YourTable
where
(unix_timestamp(date_format(fecnac,"2003-%m-%d 00:00:00")))
between (unix_timestamp(date_format(curdate(),"2003-%m-%d 00:00:00")))
and (unix_timestamp(date_format((curdate()+interval 7 day),"2003-%m-%d 00:00:00")))

fecnac is the date of birth.

Bryan,
I think your way to calculate next Friday is not correct:
SELECT DATE_ADD(CURRENT_DATE,INTERVAL (6-DAYOFWEEK(CURRENT_DATE)) DAY);
This will return the date of this Friday if the current date is Saturday.
It could be change to:
SELECT DATE_ADD(CURRENT_DATE,INTERVAL mod(6-DAYOFWEEK(current_date)+7,7) DAY);

Thank you for your example

Add your own comment.