нахождение, подходит ли годовщина в n дни в MySql
У меня есть таблица с годовщиной. Я хочу запрос, который возвращает меня строки годовщин, подходящих за следующие 10 дней. Например:
birthdate
---------
1965-10-10
1982-05-25
SELECT birthdate FROM Anniversaries WHERE mystical_magical_mumbo_jumbo <= 10
+------------+
| birthdate |
+------------+
| 1982-05-25 |
+------------+
1 row in set (0.01 sec)
Я хотел бы сохранить запрос в форме x <= 10, потому что я буду использовать тот номер 10 в других частях запроса, и если я установил его на переменную, я могу изменить его однажды везде путем замены и не иметь для переписывания запроса.
5 ответов
Как уже было сказано другими, вам нужно игнорировать год в сравнении. Функция DAYOFYEAR() - один из способов сделать это.
Вот быстрое решение. Оно вернет все дни рождения в ближайшие 10 дней, даже если сейчас конец декабря, а день рождения в следующем году.
Он НЕ обрабатывает високосные годы должным образом, поэтому он будет смещен на 1 день для дней рождения в начале марта, если этот год високосный, а человек родился не в високосный год, или наоборот. Високосные годы также приводят к тому, что дни рождения в начале января иногда отображаются на один день позже в конце декабря. Если кто-то хочет добавить поправку на високосный год, не стесняйтесь :)
SELECT birthdate
FROM Anniversaries
WHERE dayofyear(birthdate) - dayofyear(curdate()) between 0 and 10
or dayofyear(birthdate) + 365 - dayofyear(curdate()) between 0 and 10;
На основании ответа alejandrobog:
SELECT birthdate FROM Anniversaries
WHERE DATEDIFF(MAKEDATE(YEAR(CURRENT_DATE()), DAYOFYEAR(birthdate), CURRENT_DATE()) >= 0
AND DATEDIFF(MAKEDATE(YEAR(CURRENT_DATE()), DAYOFYEAR(birthdate), CURRENT_DATE()) <= 10
MAKEDATE () создает дату из года и дня года, поэтому я могу использовать его, чтобы убрать старые годы из картинки:
SELECT
anniversary
, MAKEDATE( YEAR(NOW()), DAYOFYEAR(anniversary) ) AS thisyear
FROM Anniversaries;
+-------------+-------------+
| anniversary | thisyear |
+-------------+-------------+
| 1978-07-29 | 2010-07-29 |
| 1959-04-17 | 2010-04-17 |
+-------------+-------------+
Затем я могу использовать DATEDIFF () вычисляет дни до (или от), затем:
SELECT
anniversary
, MAKEDATE( YEAR(NOW()), DAYOFYEAR(anniversary) ) AS thisyear
, DATEDIFF( MAKEDATE(YEAR(NOW()),DAYOFYEAR(anniversary)), NOW()) as days
FROM Anniversaries;
+-------------+-------------+------+
| anniversary | thisyear | days |
+-------------+-------------+------+
| 1978-07-29 | 2010-07-29 | 70 |
| 1959-04-17 | 2010-04-17 | -33 |
+-------------+-------------+------+
Предполагая, что NOW () равно 5/20.
Измените , чтобы указанное выше не работало в течение года. Одно из решений - добавить еще один расчет, где годовщина - следующий год. Здесь я жестко запрограммировал дату «2010-12-31» и использовал OR в предложении HAVING для фильтрации по дням, которые совпадают либо в этом году, либо в следующем:
SELECT birth_date
, MAKEDATE(YEAR('2010-12-31'),DAYOFYEAR(birth_date)) as anniversary
, DATEDIFF( MAKEDATE(YEAR('2010-12-31'),DAYOFYEAR(birth_date)), '2010-12-31') as days
, MAKEDATE(YEAR(NOW())+ 1,DAYOFYEAR(birth_date)) as next_anniversary
, DATEDIFF( MAKEDATE(YEAR(NOW())+ 1,DAYOFYEAR(birth_date)), '2010-12-31') as next_days
FROM Anniversaries
HAVING ( ( days <= 25 AND days > 0 ) OR next_days <= 25 );
+------------+-------------+------+------------------+-----------+
| birth_date | anniversary | days | next_anniversary | next_days |
+------------+-------------+------+------------------+-----------+
| 2010-01-23 | 2010-01-23 | -342 | 2011-01-23 | 23 |
| 1975-01-11 | 2010-01-11 | -354 | 2011-01-11 | 11 |
+------------+-------------+------+------------------+-----------+
2 rows in set (0.00 sec)
Фактически вы могли бы использовать индекс, если бы вы сохранили дату рождения отдельно от даты рождения. В столбце дня рождения будут указаны год и месяц, но у всех будет один и тот же год (например, 2000).
Затем вы должны выполнить свой запрос следующим образом:
SELECT birthdate
FROM Anniversaries
WHERE birthday >= DATE_ADD(MAKEDATE(2000, DAYOFYEAR(CURDATE())), INTERVAL 10 DAYS)
Хотя выражение не совсем то, что вам нужно, число 10 все еще находится в правой части выражения. Применение функции к правой части выражения и сохранение только столбца в левой части выражения позволяет MySQL использовать индекс в столбце дня рождения (при условии, что у вас есть индекс).
Попробуйте это
SELECT birthdate FROM Anniversaries
WHERE DATEDIFF(CURTIME(),birthdate) >= 0
AND DATEDIFF(CURTIME(),birthdate) <= 10
Проверьте это для справки MySQL DATEDIFF
DATEDIFF (expr1, expr2)
DATEDIFF () возвращает expr1 - expr2, выраженное как значение в днях от одной даты до другой. expr1 и expr2 - это выражения даты или даты и времени. При вычислении используются только части значений даты.
mysql> ВЫБРАТЬ РАЗНДАТ ('2007-12-31 23:59:59', '2007-12-30'); -> 1
mysql> ВЫБРАТЬ РАЗНДАТ ('2010-11-30 23:59:59', '2010-12-31'); -> -31