postgresql - Postgres birthdays selection

Question

I work with a Postgres database. This DB has a table with users, who have a birthdate (date field). Now I want to get all users who have their birthday in the upcoming week....

My first attempt: SELECT id FROM public.users WHERE id IN (lange reeks) AND birthdate > NOW() AND birthdate < NOW() + interval '1 week'

But this does not result, obviously because off the year. How can I work around this problem?

And does anyone know what happen to PG would go with the cases at 29-02 birthday?

Answer 1

We can use a postgres function to do this in a really nice way.

Assuming we have a table people, with a date of birth in the column dob, which is a date, we can create a function that will allow us to index this column ignoring the year. (Thanks to Zoltán B?sz?rményi):

CREATE OR REPLACE FUNCTION indexable_month_day(date) RETURNS TEXT as $BODY$
  SELECT to_char($1, 'MM-DD');
$BODY$ language 'sql' IMMUTABLE STRICT;

CREATE INDEX person_birthday_idx ON people (indexable_month_day(dob));

Now, we need to query against the table, and the index. For instance, to get everyone who has a birthday in April of any year:

SELECT * FROM people 
WHERE 
    indexable_month_day(dob) >= '04-01'
AND 
    indexable_month_day(dob) < '05-01';

There is one gotcha: if our start/finish period crosses over a year boundary, we need to change the query:

SELECT * FROM people 
WHERE 
    indexable_month_day(dob) >= '12-29'
OR 
    indexable_month_day(dob) < '01-04';

To make sure we match leap-day birthdays, we need to know if we will 'move' them a day forward or backwards. In my case, it was simpler to just match on both days, so my general query looks like:

SELECT * FROM people 
WHERE 
    indexable_month_day(dob) > '%(start)%'
%(AND|OR)%
    indexable_month_day(dob) < '%(finish)%';

I have a django queryset method that makes this all much simpler:

def birthday_between(self, start, finish):
    """Return the members of this queryset whose birthdays
    lie on or between start and finish."""
    start = start - datetime.timedelta(1)
    finish = finish + datetime.timedelta(1)
    return self.extra(where=["indexable_month_day(dob) < '%(finish)s' %(andor)s indexable_month_day(dob) > %(start)s" % {
        'start': start.strftime('%m-%d'),
        'finish': finish.strftime('%m-%d'),
        'andor': 'and if start.year == finish.year else 'or'
    }]

def birthday_on(self, date):
    return self.birthday_between(date, date)

Now, I can do things like:

Person.objects.birthday_on(datetime.date.today())

Matching leap-day birthdays only on the day before, or only the day after is also possible: you just need to change the SQL test to a `>=' or '<=', and not adjust the start/finish in the python function.