Using SQLAlchemy to filter by day in a date field

Question

I'm trying to work out how to filter by a day in a date field. If I have a date of birth field, how do I get every person that has a birthday on the 24th on all the months?

Answer 1

The extract() function creates an appropriate EXTRACT(field FROM expr) expression for the database engine in use. Extract the day field from the column and compare it to your day value.

session.query(Person).filter(extract('day', Person.birthdate) == 24).all()

• SQLAlchemy docs: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=extract#sqlalchemy.sql.expression.Extract
• PostgreSQL docs: http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
• SQLite docs: https://sqlite.org/lang_datefunc.html
• MySQL docs: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_extract