2.12: Вычисление дат в таблице MySQL
Главная Страница » Книги по PHP » MySQL уроки для начинающих с нуля » Вычисление дат в таблице MySQL
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 | 2013-07-10 | 8 |
| Claws | 1994-03-17 | 2013-07-10 | 7 |
| Buffy | 1989-05-13 | 2013-07-10 | 12 |
| Fang | 1990-08-27 | 2013-07-10 | 11 |
| Bowser | 1998-08-31 | 2013-07-10 | 11 |
| Chirpy | 1998-09-11 | 2013-07-10 | 2 |
| Whistler | 1997-12-09 | 2013-07-10 | 3 |
| Slim | 1996-04-29 | 2013-07-10 | 5 |
| Puffball | 1999-03-30 | 2013-07-10 | 2 |
+----------+------------+--------------+-----+
Здесь мы впервые встречаем несколько новых слов. Прежде всего, набор символов с последующими скобками, например YEAR(), представляет собой не ключевое слово SQL, а функцию. В скобках задаются ее аргументы. При обработке запроса вместо записи имени функции и ее аргументов будет подставлен результат ее вычисления.
Итак, давайте разбираться. Функция YEAR() выделяет часть года из даты, RIGHT0 убирает правые пять символов, которые представляют часть MM-DD (месяц и день) из даты. Часть выражения, которое сравнивает значения MM-DD, выставляется в 1 или в 0, что корректирует различие лет, если CURRENT_DATE стоит раньше в году, чем рождение (birth).
Здесь впервые появляется псевдоним для столбца. Как вы уже могли раньше заметить, MySQL выводит таблицу, в которой каждый столбец имеет в заголовке имя. Эти имена сервер берет из части SELECT вашего запроса. А теперь представьте, что будет, если он попытается поместить в заголовок таблицы вторую и третью строки запроса, которые определяют вычисляемое выражение! Таблица будет выглядеть ужасно. Поэтому это выражение надо чем-то заменить.
Полное выражение несколько перегружено, так что используется псевдоним (age), чтобы заставить столбец в выводе обрести более пригодный для чтения заголовок. Псевдоним задается ключевым словом AS после которого следует написать имя, которое следует использовать в качестве псевдонима.
Запрос работает, но результат мог бы быть просмотрен более легко, если бы строки были выведены в некотором порядке. Это может быть выполнено при добавлении предложения ORDER BY паше, чтобы отсортировать вывод:
mysql> SELECT паше, 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 | 1998-08-31 | 2013-07-10 | 11 |
| Buffy | 1989-05-13 | 2013-07-10 | 12 |
| Chirpy | 1998-09-11 | 2013-07-10 | 2 |
| Claws | 1994-03-17 | 2013-07-10 | 7 |
| Fang | 1990-08-27 | 2013-07-10 | 11 |
| Fluffy | 1993-02-04 | 2013-07-10 | 8 |
| Puffball | 1999-03-30 | 2013-07-10 | 2 |
| Slim | 1996-04-29 | 2013-07-10 | 5 |
| Whistler | 1997-12-09 | 2013-07-10 | 3 |
+----------+------------+--------------+-----+
Чтобы отсортировать вывод no возрасту (age), а не пo имени (name), примените другой параметр в предложении ORDER BY (age вместо 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 age;
+----------+------------+--------------+-----+
| name | birth | CURRENT_DATE | age |
+----------+------------+--------------+-----+
| Chirpy | 1998-09-11 | 2013-07-10 | 2 |
| Puffball | 1999-03-30 | 2013-07-10 | 2 |
| Whistler | 1997-12-09 | 2013-07-10 | 3 |
| Slim | 1996-04-29 | 2013-07-10 | 5 |
| Claws | 1994-03-17 | 2013-07-10 | 7 |
| Fluffy | 1993-02-04 | 2013-07-10 | 8 |
| Fang | 1990-08-27 | 2013-07-10 | 11 |
| Bowser | 1998-08-31 | 2013-07-10 | 11 |
| Buffy | 1989-05-13 | 2013-07-10 | 12 |
+----------+------------+--------------+-----+
Подобный запрос может использоваться, чтобы определить возраст для животных, которые умерли. Вы определяете такие случаи, проверяя, является ли значение death равным NULL или нет. Затем, для тех значений, которые не равны NULL, вычислите разницу между данными death и birth:
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 | 1998-08-31 | 1995-07-29 | 5 |
+----------+------------+------------+-----+
Запрос использует death IS NOT NULL вместо death!=NULL, потому что NULL представляет собой специальное значение, на которое обычные операторы сравнения не действуют. Подробности будут рассмотрены чуть ниже.
Что делать, если вы хотите знать, у каких животных дни рождения в следующем месяце? Для этого типа вычислений год и день не годятся. Вы просто хотите извлечь месячную часть из столбца birth. MySQL обеспечивает несколько функций для извлечения разных частей даты, типа YEAR() для года, M0NTH() для месяца и DAYOFMONTH() для дня месяца. Чтобы увидеть, как это работает, выполните простой запрос, который отображает значения 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 | 1998-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+---------------+
Вот видите, birth выводит дату рождения (год-месяц-день), a MONTH(birth) извлекает из нее среднюю часть (месяц) в виде числа.
Поиск животных с днями рождения в наступающем месяце прост. Предположим, что текущий месяц апрель. Значение month равно 4 (четвертый месяц), и вы ищете животных, родившихся в мае (month=5):
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
+----------+------------+
Однако возникает небольшое осложнение, если текущий месяц декабрь. Здесь надо искать животных, рожденных в январе.
Вы можете даже написать запрос так, чтобы это работало независимо от того, каков текущий месяц. Функция DATE_ADD() позволяет вам добавлять интервал времени к заданной дате. Если вы добавляете месяц к значению NOW() (текущая дата), а затем извлекаете месячную часть с помощью уже продемонстрированной функции MONTH(), результатом как раз и будет тот месяц, в котором надо искать дни рождения:
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
Добавляемый интервал времени задается ключевым словом INTERVAL после которого стоит значение интервала с параметром MONTH (месяц). Вместо MONTH можно указывать другие параметры: DAY (для дней) или YEAR (для лет). Обратите внимание: здесь MONTH используется не как имя функции, а как признак длины интервала. Поэтому после слова MONTH нет скобок.
