Django: Total birthdays each day for the next 30 days

Question

I've got a model similar to this:

class Person(models.Model):
    name = models.CharField(max_length=40)
    birthday = DateTimeField() # their next birthday

I would like to get a list of the total birthdays for each day for the next 30 days. So for example, the list would look like this:

[[9, 0], [10, 3], [11, 1], [12, 1], [13, 5], ... #30 entries in list

Each list entry in the list is a date number followed by the number of birthdays on that day. So for example on the 9th of May there are 0 birthdays.

UPDATES

My db is sqlite3 - will be moving to postgres in the future.

Hang on - is the birthday field their birthday in *this* year, or their actual date of birth? — Daniel Roseman, May 09 '11 at 12:39

Daniel Roseman · Answer 1 · 2011-05-09T14:10:07.687

4

from django.db.models import Count
import datetime
today = datetime.date.today()
thirty_days = today + datetime.timedelta(days=30)
birthdays = dict(Person.objects.filter(
                    birthday__range=[today, thirty_days]
                 ).values_list('birthday').annotate(Count('birthday')))


for day in range(30):
    date = today + datetime.timedelta(day)
    print "[%s, %s]" % (date, birthdays.get(date, 0))

edited May 09 '11 at 14:10

answered May 09 '11 at 13:37

Daniel Roseman

588,541
66
880
895

Using the [range](http://docs.djangoproject.com/en/dev/ref/models/querysets/#range) field lookup is definitely the way to go. – Zach Kelling May 09 '11 at 13:48
But wouldn't you want to avoid `aggregate`? The last part wouldn't work. – Zach Kelling May 09 '11 at 13:56
@Daniel Roseman - it all works up to the aggregate(Count('birthday') When I add this it just returns {}. Before adding that I get the expected objects. – ErnieP May 09 '11 at 14:07
Because aggregate would return a dict of only Count('birthday') – Zach Kelling May 09 '11 at 14:09
@zeekay @ErnieP aargh, should be `annotate`. Thanks guys. – Daniel Roseman May 09 '11 at 14:09
Oh nice, I didn't even think of annotate! – Zach Kelling May 09 '11 at 14:16
@Daniel Roseman I get a separate entry for each birthday with a count of 1 for each entry. I guess that's because birthday is a DateTime rather than Date (each birthday has a different time even if same date)? – ErnieP May 09 '11 at 14:19
@ErnieP, yes I didn't notice you were using DateTime. If you do need to store the time for whatever reason, my solution will not work. – Daniel Roseman May 09 '11 at 14:26
I know it's a bit late to this party, but now you could cast the birthday DateTime to Date, and annotate that along with the count. Something like in the following comment: – djangomachine Aug 25 '20 at 15:16
birthdays = dict(Person.objects.filter(birthday__range=[today, thirty_days]).annotate(birthday_date=Cast('birthday', DateField())).annotate(birthday_count=Count('birthday_date'))).values_list('birthday_date', 'birthday_count') – djangomachine Aug 25 '20 at 15:16

score 3 · Accepted Answer · answered May 09 '11 at 14:08

3

I would get the list of days and birthday count this way:

from datetime import date, timedelta    
today = date.today()
thirty_days = today + timedelta(days=30)

# get everyone with a birthday
people = Person.objects.filter(birthday__range=[today, thirty_days])

birthday_counts = []
for date in [today + timedelta(x) for x in range(30)]:
    # use filter to get only birthdays on given date's day, use len to get total
    birthdays = [date.day, len(filter(lambda x: x.birthday.day == date.day, people))]
    birthday_counts.append(birthdays)

answered May 09 '11 at 14:08

Zach Kelling

52,505
13
109
108

Thanks, although using the `annotate` approach to combine the count with the date is better imo. You should probably use [Daniel Roseman's answer](http://stackoverflow.com/questions/5936966/django-total-birthdays-each-day-for-the-next-30-days/5937638#5937638) instead – Zach Kelling May 09 '11 at 15:36
But as Daniel says, his method won't work for a DateTime, which is what I have. – ErnieP May 10 '11 at 05:52

score 0 · Answer 3 · answered Oct 29 '14 at 16:49

(Queryset of people with a birthday in the next X days) Found cool solution for this! For me it works!

from datetime import datetime, timedelta
import operator

from django.db.models import Q

def birthdays_within(days):

    now = datetime.now()
    then = now + timedelta(days)

    # Build the list of month/day tuples.
    monthdays = [(now.month, now.day)]
    while now <= then:
        monthdays.append((now.month, now.day))
        now += timedelta(days=1)

    # Tranform each into queryset keyword args.
    monthdays = (dict(zip(("birthday__month", "birthday__day"), t)) 
                 for t in monthdays)


    # Compose the djano.db.models.Q objects together for a single query.
    query = reduce(operator.or_, (Q(**d) for d in monthdays))

    # Run the query.
    return Person.objects.filter(query)

But it get a list of persons that have a birthday in date range. You should change a bit.

score 0 · Answer 4 · answered May 09 '11 at 12:57

Something like this --

from datetime import date, timedelta

class Person(models.Model):
    name = models.CharField(max_length=40)
    birthday = models.DateField()

    @staticmethod
    def upcoming_birthdays(days=30):
        today = date.today()
        where = 'DATE_ADD(birthday, INTERVAL (YEAR(NOW()) - YEAR(birthday)) YEAR) BETWEEN DATE(NOW()) AND DATE_ADD(NOW(), INTERVAL %S DAY)'
        birthdays = Person.objects.extra(where=where, params=[days]).values_list('birthday', flat=True)
        data = []
        for offset in range(0, days):
            i = 0
            d = today + timedelta(days=offset)
            for b in birthdays:
                if b.day == d.day and b.month == d.month:
                    i += 1
            data.append((d.day, i))
        return data

print Person.upcoming_birthdays()

I guess you don't need that custom SQL. Also functions that reference multiple objects are better on the Manager than in the Model — Eduardo, May 09 '11 at 13:08

Django: Total birthdays each day for the next 30 days

4 Answers4

Linked