напоминание о дне рождения mysql, високосный год
Я пытаюсь разобраться в наборе результатов, который дает 5 самым близким пользователям, отсортированным по предстоящему дню рождения. Это работает отлично до високосных годов играет роль. Например:
- 15-го мая - Оставлены 96 дней
- 15-го мая - Оставлены 97 дней
Главным результатом является рождение в 1987, и ниже с 1988. u_birth хранится как yyyy-mm-dd. Существует ли простой способ отсортировать эту проблему, не имея необходимость переписывать весь запрос?
SELECT u_birth, IF( DAYOFYEAR( u_birth ) >= DAYOFYEAR( NOW() ),
DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ),
DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ) +
DAYOFYEAR( CONCAT( YEAR( NOW() ), '-12-31' ) )
)
AS distance
FROM (blog_users)
WHERE `s_agehide` = 0
ORDER BY distance ASC
LIMIT 5
Этот запрос взят и изменен из mysql руководства: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#c7489
2 ответа
Очевидно, проблема возникает, если ваш алгоритм зависит от года рождения человека. Чтобы обойти это, сначала найдите следующий день рождения каждого человека после текущей даты, а затем вычислите разницу между этой датой и настоящим моментом.
SELECT u_birth, DATEDIFF(next_birthday, NOW()) AS distance FROM (
SELECT *, ADDDATE(birthday, INTERVAL birthday < DATE(NOW()) YEAR) AS next_birthday
FROM (
SELECT *, ADDDATE(u_birth, INTERVAL YEAR(NOW()) - YEAR(u_birth) YEAR) AS birthday
FROM blog_users
WHERE s_agehide = 0
) AS T1
) AS T2
ORDER BY distance ASC
LIMIT 5
Результаты:
'1992-02-29', 20
'1993-03-01', 21
'1987-05-15', 96
'1988-05-15', 96
'1988-09-18', 222
Данные теста:
CREATE TABLE blog_users (u_birth NVARCHAR(100) NOT NULL, s_agehide INT NOT NULL);
INSERT INTO blog_users (u_birth, s_agehide) VALUES
('1987-05-15', 0),
('1988-05-15', 0),
('1988-09-20', 0),
('2000-01-02', 0),
('2000-01-03', 1),
('1988-09-19', 0),
('1988-09-18', 0),
('1992-02-29', 0),
('1993-03-01', 0);
Обратите внимание, что день рождения человека, родившегося в високосный день, предполагается, что он имеет день рождения 28 февраля невисокосного года.
Кроме того, в вашем запросе не указан идентификатор пользователя. Я полагаю, вы, наверное, тоже захотите добавить это.
Не используйте dayofyear , поскольку все, что происходит после 29 февраля в високосном году, на один день больше, чем обычно. Вместо этого извлеките месяц и день и объедините их с текущим годом. Тогда сделайте сравнение.
Примерно так:
SELECT u_birth,
IF(DATE(CONCAT(YEAR(NOW()),'-',MONTH(u_birth),'-',DAY(u-birth))) >= DATE(NOW()),
...
ETA:
Чтобы мне не пришлось переписывать выражение для преобразования даты рождения в день рождения, я вставлю его в переменную. Я рекомендую вам написать функцию, которая выполняет преобразование, чтобы вы могли напрямую использовать ее в запросе.
SET @birthday= SELECT DATE(CONCAT(YEAR(NOW()),'-',MONTH(u_birth),'-',DAY(u-birth))) FROM users WHERE user_id=x; //obviously only good for one value, but use the example for the calculation
SELECT u_birth,
IF(DATE(@birthday)>=DATE(NOW()),
DATEDIFF(DATE_ADD(@birthday, INTERVAL 1 YEAR),NOW()),
DATEDIFF(NOW(),@birthday)
) as days
FROM users WHERE user_id=x;