3.3.4.5 Вычисление дат

В MySQL имеется несколько функций, реализующих арифметические операции над датами. Эти функции позволяют, например, вычислять возраст или получать части даты.

Определить возраст любого из животных в базе можно, если вычислить разницу между текущим годом и годом его рождения, а из результата вычесть единицу, если текущий день находится к началу календаря ближе, нежели день рождения животного. Приведенный ниже запрос выводит дату рождения каждого животного, его возраст и текущую дату.

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 |
+----------+------------+--------------+------+

В этом примере функция YEAR() выделяет из даты год, а RIGHT() - пять крайних справа символов, представляющих календарный день (MM-DD). Часть выражения, сравнивающая даты, выдает 1 или 0, что позволяет уменьшить результат на единицу, если текущий день (CURRENT_DATE) находится к началу календаря ближе, нежели день рождения животного. Все выражение смотрится несколько неуклюже, поэтому вместо него в заголовке соответствующего столбца результатов выводится псевдоним (age - "возраст").

Запрос неплохо работает, но разобраться в результатах было бы проще, если бы строки располагались в определенном порядке. Этого можно достичь, добавив в запрос выражение ORDER 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 |
+----------+------------+--------------+------+

Отсортировать результаты по возрасту также можно при помощи выражения ORDER BY:

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 |
+----------+------------+--------------+------+

подобный же запрос поможет определить возраст, которого достигли умершие животные на момент смерти. Выделить умерших животных можно, проверив значение поля death на предмет равенства NULL. Затем для записей, значения поля death которых не равно NULL, можно вычислить разницу между датами смерти и рождения:

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 |
+--------+------------+------------+------+

В этом запросе используется выражение death IS NOT NULL, а не death <> NULL, так как NULL - особое значение (более подробные пояснения приведены в разделе see section 3.3.4.6 Работа с значениями NULL).

А как поступать, если потребуется определить, дни рождения каких животных наступят в следующем месяце? Для таких расчетов день и год значения не имеют; из столбца, содержащего дату рождения, нас интересует только месяц. В MySQL предусмотрено несколько функций для получения частей дат - YEAR(), MONTH(), и DAYOFMONTH(). В данном случае нам подойдет функция MONTH(). Увидеть работу этой функции можно с помощью простого запроса, выводящего дату рождения birth и 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 |
+----------+------------+--------------+

Найти животных, дни рождения которых наступят в следующем месяце, тоже несложно. Предположим, что сейчас на дворе апрель. Тогда номер текущего месяца - 4, а искать надо животных, родившихся в мае (5-м месяце), таким образом:

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

Конечно, в декабре возникают некоторые осложнения. Если просто добавить единицу к номеру месяца (12) и поискать животных, родившихся в тринадцатом месяце, найти что-нибудь вряд ли удастся. Вместо этого нужно искать животных, родившихся в январе (1-м месяце).

Можно даже написать небольшой запрос, который будет работать вне зависимости от того, какой нынче месяц. Функция DATE_ADD() позволяет прибавить к дате некоторый интервал времени. Если добавить к значению, возвращаемому функцией NOW(), месяц, а затем извлечь из получившейся даты номер месяца при помощи функции MONTH(), мы получим именно тот месяц, который нам нужен:

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

Ту же задачу можно решить и другим методом - для этого нужно прибавить единицу к номеру месяца, следующего за текущим (воспользовавшись функцией расчета по модулю (MOD) для перехода к 0, если номер текущего месяца равен 12):

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

Функция MONTH возвращает число от 1 до 12, а выражение MOD(число,12) - число от 0 до 11. Поэтому операцию сложения нужно проводить после MOD(), иначе результат перепрыгнет с ноября (11) сразу на январь (1).

User Comments

The tutorial writer uses the variable
CURRENT_DATE to extract the current date up until
the introduction of the MONTH() function. At this
point the writer switches to use of the NOW()
function. I found this confusing. If both of
these means of obtaining the date can be used
here it seems like a note is in order.

I have a TIMESTAMP (YYYYMMDDHHMMSS) field in my
table. How'd I find entries 1,3,5,10 days old
(from the current day)??

Like this: SELECT something FROM table WHERE
TO_DAYS(NOW()) - TO_DAYS(timestamp_col) <= 5;

(see manual section 6.3.4 Date and Time Functions)

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.

It says in this section that there might be a
complication using the month() function if the
current month is December. Actually there shouldn't
because month() just returns the month (in integer
form) of its date argument, regardless of the current
month.

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;

I need to schedule weekly and monthly event, and my two questions are:
How can I find next Friday's date?
How can I find this month, second Wednesday's date?

current_date returns the just the date,

now() gives now, to the nearest minute. ie. it also returns the cuurent time.

Add your own comment.