Getting upcoming birthdays using 'date of birth' DateField

Question

I'm trying to get the birthdays in the upcoming 20 days, given the below Person model:

class Person(models.Model):
    dob = models.DateField()  # date of birth

There are similar questions on SO already (here and here), but these do not cover my use case, as I'm storing a date of birth instead of the next birthday or a timefield.

I've tried to do some things like the following:

from datetime import timedelta, date
today = date.today()
next_20_days = today+timedelta(days=20)
Person.objects.filter(dob__month=today.month, dob__day__range=[today.day, next_20_days.day])

... but I get FieldError: Unsupported lookup 'day' for DateField or join on the field not permitted.

When I do e.g. Person.objects.filter(dob__month=today.month, dob__day=next_20_days.day), I do get the results for exactly 20 days from now. So I potentially could go over each of the 20 days in a loop, but that seems rather ineffective.

Any idea on how to do this the proper way?

I think `timedelta` (datetime module) is what are you looking for. `Person.objects.filter(dob__range=(date.now(), date.now()+timedelta(days=20))` — user1112008, Apr 24 '15 at 23:01
@user1112008: That query you wrote is for people to be born in the next 20 days. :) What about birth year? — bmhkim, Apr 24 '15 at 23:22

score 2 · Accepted Answer · answered Oct 31 '15 at 13:02

FYI, I ended up doing the following which works for me and which does not require raw SQL. Any improvements would be welcomed :-)

# Get the upcoming birthdays in a list (which is ordered) for the amount of days specified
def get_upcoming_birthdays(person_list, days):
    person_list= person_list.distinct()  # ensure persons are only in the list once
    today = date.today()
    doblist = []
    doblist.extend(list(person_list.filter(dob__month=today.month, dob__day=today.day)))
    next_day = today + timedelta(days=1)
    for day in range(0, days):
        doblist.extend(list(person_list.filter(dob__month=next_day.month, dob__day=next_day.day, dod__isnull=True)))
        next_day = next_day + timedelta(days=1)
    return doblist

score 0 · Answer 2 · edited May 23 '17 at 11:44

Caveat: I believe calendars and time is hard. As a result, I feel obligated to warn you that I haven't rigorously tested my proposal. But of course, I think it should work. :)

Unfortunately, I think you should abandon date objects as the additional complication of year data precludes easy selects. Rather, I propose storing the birthday as a MMDD string (comparison of strings works, as long as you format them consistently). You can then compute your next_20_days and convert that to a similar MMDD string, as well as today, then use them as values to compare against.

I have three edge cases you should definitely make sure work:

Normal month rollover. (e.g., June to July)
Leap days -- don't forget to check presence as well as absence of Feb 29.
Year boundary -- you'll need to either do two queries and union the results, or do an OR query using Q objects.

Edit: See also:

and so on. I just did a Google search for "stack overflow birthday select".

Piotr Linski · Answer 3 · 2015-06-24T16:57:03.417

I have been struggling with the same issue for the past days. I think I assembled a pretty solid solution that should allow you easily to derive all the birthdays to come up for the next X days. This query runs against the database-table geburtstage (birthdays) with the following 4 fields: ID (set as primary key) vorname (firstname), nachname (lastname) and geburtstag (birthday). Just create the table, fill in some records and run the query below:

select * FROM (
    select curdate() AS today, DAY(CURDATE()) AS d_T, MONTH(CURDATE()) AS m_T, DAY(geburtstag) AS d_G, MONTH(geburtstag) AS m_G, subdate(CURDATE(),-20) AS date_20, DAY(subdate(CURDATE(),-20)) AS d_20, MONTH(subdate(CURDATE(),-20)) AS m_20, vorname, nachname, geburtstag, (YEAR(CURRENT_TIMESTAMP) - YEAR(geburtstag) +1 - CASE WHEN MONTH(CURRENT_TIMESTAMP) < MONTH(geburtstag) THEN 1 WHEN MONTH(CURRENT_TIMESTAMP) > MONTH(geburtstag) THEN 0 WHEN DAY(CURRENT_TIMESTAMP) <= DAY(geburtstag) THEN 1 ELSE 0 END) AS age, datediff(DATE_FORMAT(geburtstag,concat('%',YEAR(CURDATE()),'-%m-%d')),NOW()) AS no_of_days FROM geburtstage
    union
    select curdate() AS today, DAY(CURDATE()) AS d_T, MONTH(CURDATE()) AS m_T, DAY(geburtstag) AS d_G, MONTH(geburtstag) AS m_G, subdate(CURDATE(),-20) AS date_20, DAY(subdate(CURDATE(),-20)) AS d_20, MONTH(subdate(CURDATE(),-20)) AS m_20, vorname, nachname, geburtstag, (YEAR(CURRENT_TIMESTAMP) - YEAR(geburtstag) +1 - CASE WHEN MONTH(CURRENT_TIMESTAMP) < MONTH(geburtstag) THEN 1 WHEN MONTH(CURRENT_TIMESTAMP) > MONTH(geburtstag) THEN 0 WHEN DAY(CURRENT_TIMESTAMP) <= DAY(geburtstag) THEN 1 ELSE 0 END) AS age, datediff(DATE_FORMAT(geburtstag,concat('%',(YEAR(CURDATE())+1),'-%m-%d')),NOW()) AS no_of_days FROM geburtstage) AS upcomingbirthday
    WHERE no_of_days >=0 AND no_of_days <= 20 GROUP BY ID
    ORDER BY (m_G, d_G) < (m_T, d_T), m_G, d_G, geburtstag desc, age

Getting upcoming birthdays using 'date of birth' DateField

3 Answers3

Linked

Related