MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 the
TIMESTAMPDIFF()
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. An
alias (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 an ORDER 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 by age
rather than
name
, just use a different ORDER
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 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,
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 uses death IS NOT NULL
rather
than death <> NULL
because
NULL
is a special value that cannot be
compared using the usual comparison operators. This is
discussed 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
functions for extracting parts of dates, 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 also
simple. Suppose that the current month is April. Then the
month value is 4
and you can 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. You cannot merely 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 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
of CURDATE()
, 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(CURDATE(),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 the month value to 0
if it is
currently 12
:
mysql>SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
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
).
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' | +---------+------+----------------------------------------+