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, use theTIMESTAMPDIFF() function. Its arguments are the unit in which you want the result expressed, and the two dates for which to take the difference. The following query shows, for each pet, the birth date, the current date, and the age in years. Analias (age) is used to make the final output column label more meaningful.
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Fluffy | 1993-02-04 | 2003-08-19 | 10 || Claws | 1994-03-17 | 2003-08-19 | 9 || Buffy | 1989-05-13 | 2003-08-19 | 14 || Fang | 1990-08-27 | 2003-08-19 | 12 || Bowser | 1989-08-31 | 2003-08-19 | 13 || Chirpy | 1998-09-11 | 2003-08-19 | 4 || Whistler | 1997-12-09 | 2003-08-19 | 5 || Slim | 1996-04-29 | 2003-08-19 | 7 || Puffball | 1999-03-30 | 2003-08-19 | 4 |+----------+------------+------------+------+ 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 anORDER BY name clause to sort the output by name:
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Bowser | 1989-08-31 | 2003-08-19 | 13 || Buffy | 1989-05-13 | 2003-08-19 | 14 || Chirpy | 1998-09-11 | 2003-08-19 | 4 || Claws | 1994-03-17 | 2003-08-19 | 9 || Fang | 1990-08-27 | 2003-08-19 | 12 || Fluffy | 1993-02-04 | 2003-08-19 | 10 || Puffball | 1999-03-30 | 2003-08-19 | 4 || Slim | 1996-04-29 | 2003-08-19 | 7 || Whistler | 1997-12-09 | 2003-08-19 | 5 |+----------+------------+------------+------+ To sort the output byage rather thanname, just use a differentORDER BY clause:
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Chirpy | 1998-09-11 | 2003-08-19 | 4 || Puffball | 1999-03-30 | 2003-08-19 | 4 || Whistler | 1997-12-09 | 2003-08-19 | 5 || Slim | 1996-04-29 | 2003-08-19 | 7 || Claws | 1994-03-17 | 2003-08-19 | 9 || Fluffy | 1993-02-04 | 2003-08-19 | 10 || Fang | 1990-08-27 | 2003-08-19 | 12 || Bowser | 1989-08-31 | 2003-08-19 | 13 || Buffy | 1989-05-13 | 2003-08-19 | 14 |+----------+------------+------------+------+ 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 thedeath value isNULL. Then, for those with non-NULL values, compute the difference between thedeath andbirth values:
mysql> SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) 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 usesdeath IS NOT NULL rather thandeath <> NULL becauseNULL is a special value that cannot be compared using the usual comparison operators. This is discussed later. SeeSection 4.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 thebirth column. MySQL provides several functions for extracting parts of dates, such asYEAR(),MONTH(), andDAYOFMONTH().MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of bothbirth andMONTH(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 also simple. Suppose that the current month is April. Then the month value is4 and you can look for animals born in May (month5) 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. You cannot merely add one to the month number (12) and look for animals born in month13, because there is no such month. Instead, you look for animals born in January (month1).
You can write the query so that it works no matter what the current month is, so that you do not have to use the number for a particular month.DATE_ADD() enables you to add a time interval to a given date. If you add a month to the value ofCURDATE(), then extract the month part withMONTH(), the result produces the month in which to look for birthdays:
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)); A different way to accomplish the same task is to add1 to get the next month after the current one after using the modulo function (MOD) to wrap the month value to0 if it is currently12:
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;MONTH() returns a number between1 and12. AndMOD(something,12) returns a number between0 and11. So the addition has to be after theMOD(), otherwise we would go from November (11) to January (1).
If a calculation uses invalid dates, the calculation fails and produces warnings:
mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;+-------------------------------+| '2018-10-31' + INTERVAL 1 DAY |+-------------------------------+| 2018-11-01 |+-------------------------------+mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;+-------------------------------+| '2018-10-32' + INTERVAL 1 DAY |+-------------------------------+| NULL |+-------------------------------+mysql> SHOW WARNINGS;+---------+------+----------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------+| Warning | 1292 | Incorrect datetime value: '2018-10-32' |+---------+------+----------------------------------------+