MySQL / 5. Работа с датами и временем

Вычисление возраста

Задача
Вы хотите узнать, сколько кому-то лет.

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

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

Обсуждение
Определение возраста относится к операциям вычисления интервалов, но в данном случае недостаточно простого вычисления разности дат в днях и деления ее на 365. Нельзя забывать о високосных годах. (Интервал с 1995-03-01 по 1996-02-29 покрывает 365 дней, но в терминах возраста – это не год.) Если выполнять деление на 365.25, вычисление будет более точным, но все же корректным не для всех дат. Вместо этого мы будем определять разность дат в годах, а затем при необходимости корректировать ее, принимая во внимание относительное положение дат внутри календарного года. (Пусть, например, Гретхен Смит родилась 14 апреля 1942 года. Чтобы узнать, сколько ей сейчас лет, необходимо посмотреть, в какую часть календарного года попадает текущая дата: до 13-го апреля включительно Гретхен будет на год моложе, чем начиная с 14-го.) В данном разделе показано, как вычислять возраст в годах и месяцах.

Определение возраста в годах
В общем случае, если вы знаете дату рождения birth, то возраст в годах на дату d можно вычислить так:

if (d occurs earlier in the year than birth)
age = YEAR(d) - YEAR(birth) - 1
if (d occurs on or later in the year than birth)
age = YEAR(d) - YEAR(birth)

Оба раза выполняется одно и то же вычисление – определение разности дат в годах. Разница лишь в относительном порядке дат внутри календарного года. Этот порядок невозможно установить при помощи функции DAYOFYEAR(),поскольку ее результат имеет смысл только для дат, относящихся к годам с одинаковым количеством дней. Как видно из результатов следующего запроса, в разных годах для разных дат функция DAYOFYEAR() может давать одинаковые значения:

mysql> SELECT DAYOFYEAR('1995-03-01'), DAYOFYEAR('1996-02-29');

+-------------------------+-------------------------+
| DAYOFYEAR('1995-03-01') | DAYOFYEAR('1996-02-29') |
+-------------------------+-------------------------+
| 60 | 60 |
+-------------------------+-------------------------+

Здесь помогает то, что строки дат ISO сравниваются как раз так, как нам нужно. Точнее, используется тот факт, что пять самых правых символов значения даты, представляющие месяц и день, сравниваются соответственно:

mysql> SELECT RIGHT('1995-03-01',5), RIGHT('1996-02-29',5);

+-----------------------+-----------------------+
| RIGHT('1995-03-01',5) | RIGHT('1996-02-29',5) |
+-----------------------+-----------------------+
| 03-01 | 02-29 |
+-----------------------+-----------------------+

mysql> SELECT IF('02-29' < '03-01','02-29','03-01') AS earliest;

+----------+
| earliest |
+----------+
| 02-29 |
+----------+

То есть можно выполнить проверку на наиболее раннюю из дат d1 и d2 так: RIGHT(d2,5) < RIGHT(d1,5)

В зависимости от результатов проверки выражение принимает значение 1 или 0, так что мы можем использовать результат сравнения < для вычисления возраста в годах:

YEAR(d2) - YEAR(d1) - (RIGHT(d2,5) < RIGHT(d1,5))

Чтобы было очевидно, как вычисляется выражение, поместим его внутри функции IF(), которая явно возвращает 1 или 0:

YEAR(d2) - YEAR(d1) - IF(RIGHT(d2,5) < RIGHT(d1,5),1,0)

Применим формулу для вычисления возраста человека, родившегося 1965-03-01, на начало 1975 года. Будем выводить нескорректированную разность в годах, значение корректировки и итоговый возраст:

mysql> SET @birth = '1965-03-01';
mysql> SET @target = '1975-01-01';
mysql> SELECT @birth, @target,

-> YEAR(@target) - YEAR(@birth) AS 'difference',
-> IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0) AS 'adjustment',
-> YEAR(@target) - YEAR(@birth)-> - IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0)
-> AS 'age';

+------------+------------+------------+------------+------+
| @birth | @target | difference | adjustment | age |
+------------+------------+------------+------------+------+
| 1965-03-01 | 1975-01-01 | 10 | 1 | 9 |
+------------+------------+------------+------------+------+

Давайте опробуем методику вычисления возраста в годах на таблице sibling, в которой перечислены даты рождения Гретхен Смит (Gretchen Smith) и ее братьев Вильбура (Wilbur) и Франца (Franz):

+----------+------------+
| name | birth |
+----------+------------+
| Gretchen | 1942-04-14 |
| Wilbur      | 1946-11-28 |
| Franz       | 1953-03-05 |
+----------+------------+

Получим ответы на следующие вопросы:

• Сколько лет Смитам на сегодняшний день?

mysql> SELECT name, birth, CURDATE() AS today,
-> YEAR(CURDATE()) - YEAR(birth)
-> - IF(RIGHT(CURDATE(),5) < RIGHT(birth,5),1,0)
-> AS 'age in years'
-> FROM sibling;

+----------+------------+------------+--------------+
| name | birth | today | age in years |
+----------+------------+------------+--------------+
| Gretchen | 1942-04-14 | 2002-07-15 | 60 |
| Wilbur      | 1946-11-28 | 2002-07-15 | 55 |
| Franz       | 1953-03-05 | 2002-07-15 | 49 |
+----------+------------+------------+--------------+

• Сколько лет было Гретхен и Вильбуру, когда родился Франц?

mysql> SELECT name, birth, '1953-03-05' AS 'Franz'' birthday',
-> YEAR('1953-03-05') - YEAR(birth)
-> - IF(RIGHT('1953-03-05',5) < RIGHT(birth,5),1,0)
-> AS 'age in years'
-> FROM sibling WHERE name != 'Franz';

+----------+------------+-----------------+--------------+
| name | birth | Franz' birthday | age in years |
+----------+------------+-----------------+--------------+
| Gretchen | 1942-04-14 | 1953-03-05 | 10 |
| Wilbur      | 1946-11-28 | 1953-03-05 | 6 |
+----------+------------+-----------------+--------------+

При выполнении подобных вычислений не забывайте о том, что сравнения частей вида MM-DD строк выдают правильные результаты, только если используются значения ISO, такие как 1987-07-01, но не значения, близкие-к-формату-ISO, такие как 1987-7-1. Например, следующее сравнение выводит лексически правильный результат, но некорректный в терминах времени:

mysql> SELECT RIGHT('1987-7-1',5) < RIGHT('1987-10-01',5);

+---------------------------------------------+
| RIGHT('1987-7-1',5) < RIGHT('1987-10-01',5) |
+---------------------------------------------+
| 0 |
+---------------------------------------------+

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

Определение возраста в месяцах
Вычисление возраста в месяцах – это процедура, аналогичная вычислению в годах, только разность в годах умножается на 12, добавляется разность в месяцах и учитывается относительное положение дней двух дат в пределах календарного месяца. В этом случае необходимо по отдельности использовать части месяца и дня каждой даты, так что можно не сравнивать части MM-DD строк дат, а извлечь их, используя функции MONTH() и DAYOFMONTH(). Текущий возраст членов семьи Смитов в месяцах можно вычислить так:

mysql> SELECT name, birth, CURDATE() AS today,
-> (YEAR(CURDATE()) - YEAR(birth)) * 12
-> + (MONTH(CURDATE()) - MONTH(birth))
-> - IF(DAYOFMONTH(CURDATE()) < DAYOFMONTH(birth),1,0)
-> AS 'age in months'
-> FROM sibling;

+----------+------------+------------+---------------+
| name | birth | today | age in months |
+----------+------------+------------+---------------+
| Gretchen | 1942-04-14 | 2002-07-15 | 723 |
| Wilbur      | 1946-11-28 | 2002-07-15 | 667 |
| Franz       | 1953-03-05 | 2002-07-15 | 592 |
+----------+------------+------------+---------------+

Статьи по MySQL на эту тему:

Выбор записей по временным характеристикам
Вывод значений TIMESTAMP в удобном для чтения виде
Вычисление длины месяца
Вычисления для високосных годов
Вычисления со значениями TIMESTAMP Задача

Вернуться в раздел: MySQL / 5. Работа с датами и временем