MySQL SELECT предстоящие дни рождения

Я пытаюсь написать запрос, чтобы выбрать пользователей базы данных, чьи дни рождения находятся в следующие 7 дней.

Я провел много исследований, но я не могу придумать рабочее решение.

Поле дня рождения хранится как varchar, например '04/16/93 ', есть ли способ работать с этим?

Это то, что у меня есть до сих пор:

SELECT * 
FROM   `PERSONS` 
WHERE  `BIRTHDAY` > DATEADD(DAY, -7, GETDATE())

Я должен был сделать это более ясным, я пытаюсь найти дни рождения, а не даты рождения. Так что я просто ищу дни и месяцы, а не годы.

Ответы

Ответ 1

Чтобы получить все дни рождения в ближайшие 7 дней, добавьте разницу в году между датой рождения и сегодняшним днем до даты рождения, а затем найдите, будет ли она в течение следующих семи дней.

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

Если вы хотите исключить сегодняшние дни рождения, просто измените > на >=

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) >= DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- Same as above query with another way to exclude today birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) YEAR) <> CURDATE();


-- Same as above query with another way to exclude today birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND (MONTH(birthday) <> MONTH(CURDATE()) OR DAY(birthday) <> DAY(CURDATE()));

Вот DEMO всех запросов

Ответ 2

Это мое решение. Он также работает, если дата рождения 1 января, а сегодня - 31 декабря.

SELECT `id`, `name`, `dateofbirth`,
    DATE_ADD(
        dateofbirth, 
        INTERVAL IF(DAYOFYEAR(dateofbirth) >= DAYOFYEAR(CURDATE()),
            YEAR(CURDATE())-YEAR(dateofbirth),
            YEAR(CURDATE())-YEAR(dateofbirth)+1
        ) YEAR
    ) AS `next_birthday`
FROM `user` 
WHERE 
    `dateofbirth` IS NOT NULL
HAVING 
    `next_birthday` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY `next_birthday`
LIMIT 1000;

Ответ 3

попробуйте следующее:

 Select * From persons
 where (DayOfYear(birthday) >= 7 
         And DayOfYear(birthday) - DayOfYear(curdate()) Between 0 and 6) Or
       (MOD(YEAR(curDate()),4) = 0) And MOD(YEAR(curDate()),100) != 0
        And (DayOfYear(birthday) + 366 - DayOfYear(curdate())) % 366 < 7) Or
         (DayOfYear(birthday) + 365 - DayOfYear(curdate())) % 365 < 7)

Ответ 4

Мне удалось заставить его работать с этим запросом. В основном благодаря помощи ответа Лобо.

SELECT * 
FROM  persons 
WHERE  DATE_ADD(STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR(CURDATE())-YEAR(STR_TO_DATE(birthday, '%m/%d/%Y')) YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

Ответ 5

Я нашел, что этот код работает очень хорошо:

DATE_ADD(user_birthdate, INTERVAL YEAR(FROM_DAYS(DATEDIFF(CURDATE(), user_birthdate)-1)) + 1 YEAR) AS next_birthday

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

он основан на ответе Роберта Эйзеля, который вы можете найти здесь: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

p.s.

с этим решением вы могли бы получить людей, у которых был день рождения вчера (это потому, что -1 в расчете FROM_DAYS, но это необходимо из-за високосных лет). это не должно вас слишком беспокоить, так как вы хотите только 7 дней головой, поэтому просто добавьте следующее условие:

HAVING next_birthday BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)

Ответ 6

Принятый выше ответ от Lobo ошибочен в том, что если запрос выполняется 31 декабря, а у пользователя есть день рождения 1 января, они не будут сопоставлены.

Вы должны добавить логику в запрос, чтобы, если день рождения уже прошел в этом году, вы смотрите день рождения NEXT YEAR, а не в этом году. Подробности ниже:

SELECT *, IF( DAYOFYEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) < DAYOFYEAR( NOW() ),
DATE_ADD( STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR( CURDATE() ) - YEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) + 1 YEAR ),
DATE_ADD( STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR( CURDATE() ) - YEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) YEAR ) )
AS nextBirthday FROM persons
WHERE nextBirthday BETWEEN CURDATE() AND DATE_ADD( CURDATE(), INTERVAL 7 DAY);

Ответ 7

Это то, что я сделал, когда столкнулся с одной и той же проблемой:

select * from users 
where ( month(date_of_birth) > month(CURDATE()) 
 and month(date_of_birth) < month(ADDDATE(CURDATE(),30)) ) 
 or ( month(CURDATE()) = month(date_of_birth) and day(date_of_birth) >= day(CURDATE()) 
 or month(ADDDATE(CURDATE(),30)) = month(date_of_birth) and day(date_of_birth) <= day(ADDDATE(CURDATE(),30)) ) 
 or ( year(CURDATE()) > year(ADDDATE(CURDATE(),30)) and month(date_of_birth) < month(CURDATE()) and month(date_of_birth) > month(ADDDATE(CURDATE(),30)) )

Ответ 8

При попытке получить список, у которого есть свои b'days, предстоящие в течение некоторого времени, мы можем выполнить несколько проблем.

Когда есть високосный год, есть вероятность, что условие, которое вы не справляетесь с високосными годами. Следующая проблема может быть как сегодня 2016-12-30, и вам нужно b'days в течение следующих 7 дней. Итак, конец периода в году 2017. Некоторое условие терпит неудачу в этом случае. Это очень важные тестовые примеры.

Большинство исправлений в этом потоке используют DAYOFYEAR(), который терпит неудачу, когда вы находитесь в високосный год.

например.

DAYOFYEAR('2016-03-01 00:00:00') 61.
DAYOFYEAR('2015-03-01 00:00:00') 60

Простейшим и самым простым для понимания способом является это.

Рассчитайте следующий предстоящий b'day для пользователя.
Затем проверьте, пришел ли день в нашем диапазоне.

Это работает в високосные годы, а также диапазон дат за два года.

SELECT  * 
FROM    `PERSONS` 
WHERE  
    /* 
       Here we calculate the next coming b'day for user 
       and check if it is between our span 
    */
    CONCAT(IF( 
            CONCAT( YEAR(CURDATE()), substring(`BIRTHDAY`, 5, length(`BIRTHDAY`))) < CURDATE(), 
            YEAR(CURDATE()) + 1, /* Adds an year if already past */
            YEAR(CURDATE())  /* Use this year if it is upcoming */
         ), substring(`BIRTHDAY`, 5, length(`BIRTHDAY`))) 
    BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL @tot DAY)

PS: Также это лучшее решение, если вы хотите заказать их на основе b'days. Вам просто нужно добавить это как поле.

Ответ 9

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

SELECT first_name, last_name, birth_date,
    IF (DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T')) < DAYOFYEAR(DATE_FORMAT(NOW(),'1980-%m-%d %T')) ,
          DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T'))+368 ,
          DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T'))) AS upcomming
FROM users
WHERE birth_date IS NOT NULL AND birth_date !=0
ORDER BY upcomming ASC 
LIMIT 0, 10

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

P.S.: Если вы не хотите показывать дни рождения, просто добавьте +1 после DAYOFYEAR(DATE_FORMAT(NOW(),'1980-%m-%d %T'))

Ответ 10

SELECT * from persons as p
WHERE   
MOD( DAYOFYEAR(p.bday) - DAYOFYEAR(CURRENT_DATE()) + 366, 366)  
BETWEEN 0 and 7  
ORDER by DAYOFYEAR(p.bday) ASC

Это работает для меня.

Ответ 11

Основываясь на Лобо, чтобы решить високосные годы

SELECT * FROM users
WHERE DATE_ADD(dob,INTERVAL YEAR(CURDATE())-YEAR(dob)
  + IF(MONTH(CURDATE()) > MONTH(dob), 1,
     IF(MONTH(CURDATE()) = MONTH(dob) AND DAY(CURDATE()) > DAY(dob), 1, 0))
       YEAR)
        BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)

Ответ 12

Таким образом, другой подход, который вы можете предпринять, - сделать немного больше работы, прежде чем ударить по слою базы данных. Например, я недавно сделал это в проекте Laravel (PHP) и Postgres. Я в основном создал массив дат (т.е. Следующие 7 дней) и сделал запрос where in, чтобы найти пользователей с днями рождения в эти даты.

User::whereNotNull('birth_date')
    ->where('birth_date', '<=', $endDate)
    ->whereIn(DB::raw("to_char(birth_date, 'MMDD')"), Carbon::range($startDate, $endDate)->map(function ($date) {
        return $date->format('md');
    }))->get();