SQLAlchemy filter by upcoming birthday / annual anniversary

user_2682863 0
Viewed 454

Env: python 3.8, flask-sqlalchemy, postgres

class User(db.Model):
    name = db.Column(db.Text)
    birthday = db.Column(db.DateTime)

    @classmethod
    def upcoming_birthdays(cls):
        return (cls.query
                .filter("??")
                .all()
                )

I'd like to create a sqlalchemy query that filters users with an upcoming birthday within X number of days. I thought about using the extract function, to extract the month and day from the birthday, but that doesn't work for days at the end of the month or year. I also thought about trying to convert the birthday to a julian date for comparison, but I don't know how to go about that.

For example if today was August 30, 2020 it would return users with birthdays

  • September 1 1995
  • August 31 2010 .... etc

Thanks for your help

flask-sqlalchemy sqlalchemy python
2 Answers

You can do achieve your goal of having a simple query as below:

q = (
    db.session.query(User)
    .filter(has_birthday_next_days(User.birthday, 7))
)

This is not a @classmethod on User, but you can transform the solution to one if so you desire.

What is left to do is to actually implement the has_birthday_next_days(...), which is listed below and is mostly the documentation of the principle:

def has_birthday_next_days(sa_col, next_days: int = 0):
    """ 
    sqlalchemy expression to indicate that an sa_col (such as`User.birthday`)
    has anniversary within next `next_days` days.

    It is implemented by simply checking if the 'age' of the person (in years)
    has changed between today and the `next_days` date.
    """
    return age_years_at(sa_col, next_days) > age_years_at(sa_col)

There can be multiple implementations of the age_years_at and below is just one possibility, speficic to postgresql (including the required imports):

import datetime
import sqlalchemy as sa

def age_years_at(sa_col, next_days: int = 0):
    """
    Generates a postgresql specific statement to return 'age' (in years)'
    from an provided field either today (next_days == 0) or with the `next_days` offset.
    """
    stmt = func.age(
        (sa_col - sa.func.cast(datetime.timedelta(next_days), sa.Interval))
        if next_days != 0
        else sa_col
    )
    stmt = func.date_part("year", stmt)
    return stmt

Finally, the desired query q = db.session.query(User).filter(has_birthday_next_days(User.birthday, 30)) generates:

SELECT "user".id,
       "user".name,
       "user".birthday
FROM   "user"
WHERE   date_part(%(date_part_1)s, age("user".birthday - CAST(%(param_1)s AS INTERVAL))) 
      > date_part(%(date_part_2)s, age("user".birthday))

{'date_part_1': 'year', 'param_1': datetime.timedelta(days=30), 'date_part_2': 'year'}

Bonus: Having implemented this using generic functions, it can be used not only on the User.birthday column but any other type compatible value. Also the functions can be used separately in both the select and where parts of the statement. For example:

q = (
    db.session.query(
        User,
        age_years_at(User.birthday).label("age_today"),
        age_years_at(User.birthday, 7).label("age_in_a_week"),
        has_birthday_next_days(User.birthday, 7).label("has_bday_7-days"),
        has_birthday_next_days(User.birthday, 30).label("has_bday_30-days"),
    )
    .filter(has_birthday_next_days(User.birthday, 30))
)
user_995940

Your original idea about extracting day and month is good.
If you start with e.g. a birthday September 1 1995, you can move that day and month to the current year (i.e. September 1 2020) and afterwards check if that date is within your specified range (between today and today+X days).

As for the days at the end of the year - you can solve that by moving the birthday day and month not only to the current year, but also to the next one.
For example, let's say that today is December 30 2020 and you have a birthday January 2 1995. Then you would check whether either one of the dates January 2 2020 or January 2 2021 are within your specified range.

from datetime import date, timedelta
from sqlalchemy import func,or_


@classmethod
def upcoming_birthdays(cls):
    dateFrom = date.today()
    dateTo = date.today() + timedelta(days=5)
    thisYear = dateFrom.year
    nextYear = dateFrom.year + 1
    return (cls.query
            .filter(
                or_(
                    func.to_date(func.concat(func.to_char(cls.birthday, "DDMM"), thisYear), "DDMMYYYY").between(dateFrom,dateTo),
                    func.to_date(func.concat(func.to_char(cls.birthday, "DDMM"), nextYear), "DDMMYYYY").between(dateFrom,dateTo)
                )
            )
            .all()
            )
user_112406420
Related