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

I'm not overly confident in this, but it seems to work in my testing. The key here is the OVERLAPS operator, and some date arithmetic.

I assume you have a table:

create temporary table birthdays (name varchar, bday date);

Then I put some stuff into it:

insert into birthdays (name, bday) values 
('Aug 24', '1981-08-24'), ('Aug 04', '1982-08-04'), ('Oct 10', '1980-10-10');

This query will give me the people with birthdays in the next week:

select * from 
  (select *, bday + date_trunc('year', age(bday)) + interval '1 year' as anniversary from birthdays) bd 
where 
  (current_date, current_date + interval '1 week') overlaps (anniversary, anniversary)

The date_trunc truncates the date at the year, so it should get you up to the current year. I wound up having to add one year. This suggests to me I have an off-by-one in there for some reason. Perhaps I just need to find a way to get dates to round up. In any case, there are other ways to do this calculation. age gives you the interval from the date or timestamp to today. I'm trying to add the years between the birthday and today to get a date in the current year.

The real key is using overlaps to find records whose dates overlap. I use the anniversary date twice to get a point-in-time.

Answer 2

I know this post is old, but I had the same issue and came up with this simple and elegant solution: It is pretty easy with age() and accounts for lap years... for the people who had their birthdays in the last 20 days:

SELECT * FROM c 
WHERE date_trunc('year', age(birthdate)) != date_trunc('year', age(birthdate + interval '20 days'))

Answer 3

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.

Answer 4

In case you want it to work with leap years:

create or replace function birthdate(date)
  returns date
as $$
  select (date_trunc('year', now()::date)
         + age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval
         )::date;
$$ language sql stable strict;

Then:

where birthdate(birthdate) between current_date
                            and current_date + interval '1 week'

See also:

Getting all entries who's Birthday is today in PostgreSQL

Answer 5

Finally, to show the upcoming birthdays of the next 14 days I used this:

SELECT 
    -- 14 days before birthday of 2000
    to_char( to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') - interval '14 days' , 'YYYY-MM-dd')  as _14b_b2000,
    -- birthday of 2000
    to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') as date_b2000,
    -- current date of 2000
    to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') as date_c2000,
    -- 14 days after current date of 2000
    to_char( to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') + interval '14 days' , 'YYYY-MM-dd') as _14a_c2000,
    -- 1 year after birthday of 2000
    to_char( to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') + interval '1 year' , 'YYYY-MM-dd') as _1ya_b2000
FROM c
WHERE 
    -- the condition 
    -- current date of 2000 between 14 days before birthday of 2000 and birthday of 2000
    to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') between 
        to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') - interval '14 days' and 
        to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') 
    or 
    -- 1 year after birthday of 2000 between current date of 2000 and 14 days after current date of 2000
    to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') + interval '1 year' between 
        to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') and 
        to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') + interval '14 days' 
;

So: To solve the leap-year issue, I set both birthdate and current date to 2000, and handle intervals only from this initial correct dates.

To take care of the near end/beginning dates, I compared first the 2000 current date to the 2000 birthday interval, and in case current date is at the end of the year, and the birthday is at the beginning, I compared the 2001 birthday to the 2000 current date interval.

Answer 6

Exemple: birthdate between: jan 20 and feb 10

SELECT * FROM users WHERE TO_CHAR(birthdate, '1800-MM-DD') BETWEEN '1800-01-20' AND '1800-02-10'

Why 1800? No matter may be any year;

In my registration form, I can inform the date of birth (with years) or just the birthday (without year), in which case I saved as 1800 to make it easier to work with the date