Выбор записей по временным характеристикам

Задача
Вы хотите выбирать записи по их временным характеристикам.

Решение
Используйте условие для времени или даты в инструкции WHERE. Можно сравнивать непосредственно значения столбцов с известными значениями. А можно применить к значениям столбцов функцию, чтобы преобразовать их в более удобный для проверок формат, например, можно использовать MONTH() для проверки составляющей месяца дат.

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

Сравнение дат друг с другом
Следующие запросы находят записи таблицы date_val, относящиеся к периоду до 1900 года или к 1900-м годам:

mysql> SELECT d FROM date_val where d < '1900-01-01';

+--------------+
| d |
+--------------+
| 1864-02-28 |
+--------------+

mysql> SELECT d FROM date_val where d BETWEEN '1900-01-01' AND '1999-12-31';

+--------------+
| d |
+--------------+
| 1900-01-15 |
| 1987-03-05 |
| 1999-12-31 |
+--------------+

Если вы работаете с более ранней, чем 3.23.9, версией MySQL, то инструкция BETWEEN может не всегда корректно работать со строковыми литералами дат, имеющими формат не-ISO.


Например, может произойти сбой при выполнении такого запроса:

SELECT d FROM date_val WHERE d BETWEEN '1960-3-1' AND '1960-3-15';
Если это произошло, попробуйте преобразовать даты в формат ISO:

SELECT d FROM date_val WHERE d BETWEEN '1960-03-01' AND '1960-03-15';

Также можно переписать выражение, используя два явных сравнения:

SELECT d FROM date_val WHERE d >= '1960-03-01' AND d <= '1960-03-15';

Если нет точной даты, которую хотелось бы использовать в инструкции WHERE, ее можно вычислить при помощи выражения. Например, чтобы найти в таблице history записи о событиях, произошедших ровно 50 лет назад, выполним такой запрос:

SELECT * FROM history WHERE d = DATE_SUB(CURDATE(),INTERVAL 50 YEAR);

Такие сведения часто публикуются в газетных колонках типа «в этот день много лет назад» (на самом деле, запрос выводит те события, которые отпраздновали свой n-й юбилей). Если вы хотите извлечь события, которые случились «в этот день» не в каком-то конкретном году, а в любом, запрос будет немного другим. Необходимо найти записи, соответствующие данному календарному дню, без учета года.

Вычисленные даты можно использовать для проверки на вхождение в диапазон.


Например, для нахождения дат последних шести лет используем функцию DATE_SUB() для вычисления граничной даты:

mysql> SELECT d FROM date_val WHERE d >= DATE_SUB(CURDATE(),INTERVAL 6 YEAR);

+--------------+
| d |
+--------------+
| 1999-12-31 |
| 2000-06-04 |
+--------------+

Обратите внимание на то, что в выражении инструкции WHERE столбец даты d расположен обособленно – он один находится слева от оператора сравнения.

Если столбец индексирован, то обычно такая форма запроса обрабатываетсяв MySQL наиболее эффективно. Можно было бы записать инструкцию WHERE другим способом, который логически эквивалентен предыдущему, но выполняется медленнее:

... WHERE DATE_ADD(d,INTERVAL 6 MONTH) >= CURDATE();

В данном случае столбец d используется внутри выражения, то есть извлекаться для вычисления и проверки выражения будет каждая строка – получается, что индекс не востребован.

Иногда не сразу понятно, как переформулировать сравнение так, чтобы столбец даты оказался изолированным по одну сторону от оператора сравнения. Например, такая инструкция WHERE использует в сравнении только часть столбца дат:

...


WHERE YEAR(d) >= 1987 AND YEAR(d) <= 1991;

Чтобы изменить первое сравнение, уберем вызов YEAR() и заменим правую часть на полное значение даты:

... WHERE d >= '1987-01-01' AND YEAR(d) <= 1991;

Изменить второе сравнение несколько сложнее. Можно, как и в первом случае, избавиться от YEAR(), но нельзя просто добавить к году в правой части -01-01. Это приведет к следующему некорректному результату:

... WHERE d >= '1987-01-01' AND d <= '1991-01-01';

Замену нельзя считать успешной, так как новую проверку не пройдут даты с 1991-01-02 по 1991-12-31, удовлетворявшие условиям старого теста. Для того чтобы корректно переписать второе сравнение, нужно использовать один из вариантов:

... WHERE d >= '1987-01-01' AND d <= '1991-12-31';
... WHERE d >= '1987-01-01' AND d < '1992-01-01';

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

• Хранить для каждой записи дату ее создания (создайте столбец TIMESTAMP или используйте NOW().


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

DELETE FROM имя_таблицы WHERE create_date < DATE_SUB(NOW(),INTERVAL n DAY);

• Хранить в каждой записи явную дату истечения срока хранения, вычисляя ее при помощи DATE_ADD() при создании записи. Запись, которая должна быть удалена через n дней, могла бы создаваться так:

INSERT INTO имя_таблицы (expire_date,...)
VALUES(DATE_ADD(NOW(),INTERVAL n DAY),...);

Чтобы выполнить операцию удаления устаревших записей, сравните дату истечения срока хранения записи с текущей датой:

DELETE FROM имя_таблицы WHERE expire_date < NOW()

Сравнение значений времени
Сравнения, в которых участвуют значения времени, аналогичны сравнениям дат. Например, чтобы найти значения времени, относящиеся к периоду с 9 часов утра до 2 часов дня, используем одно из выражений:

... WHERE t1 BETWEEN '09:00:00' AND '14:00:00';
... WHERE HOUR(t1) BETWEEN 9 AND 14;

Для индексированного столбца TIME эффективнее первый способ.


Второй же хорош тем, что он работает не только для столбцов TIME, но и для DATETIME и TIMESTAMP.

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

• У кого сегодня день рождения? Необходимо установить соответствие определенному календарному дню, поэтому извлекаем из даты составляющие месяца и дня, игнорируя год при выполнении сравнений:

... WHERE MONTH(d) = MONTH(CURDATE()) AND DAYOFMONTH(d) = DAYOFMONTH(CURDATE());

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

Хочется попробовать использовать для решения задачи «в этот день» функцию DAYOFYEAR(), чтобы упростить запрос. Но DAYOFYEAR() плохо работает с високосными годами. Наличие 29 февраля портит значения дней с марта по декабрь.

• У кого день рождения в этом месяце? В данном случае будем проверять только месяц:

... WHERE MONTH(d) = MONTH(CURDATE());

• У кого будет день рождения в следующем месяце? Хитрость в том, что для получения значения месяца и проверки его на соответствие нельзя просто добавить единичку к текущему месяцу. Если текущий месяц – декабрь, вы получите тринадцатый месяц. Для того чтобы получить январь (первый месяц), воспользуйтесь одним из выражений:

... WHERE MONTH(d) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
... WHERE MONTH(d) = MOD(MONTH(CURDATE()),12)+1;

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вывод значений TIMESTAMP в удобном для чтения виде
Вычисление возраста
Вычисление длины месяца
Вычисление интервалов между значениями времени
Вычисления для високосных годов

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