Sqlalchemy: how to filter date field?

python

sqlalchemy

date-filters

database-queries

byAnton Shumikhin·Oct 11, 2024

To swiftly filter a date field in SQLAlchemy, leverage the filter function and the datetime.date object. Here's an example with a User model and created_at date column:

from your_model import User
from datetime import date

# Who joined us on New Year's day of 2023? Let's find them!
users = session.query(User).filter(User.created_at == date(2023, 1, 1)).all()

Just replace date(2023, 1, 1) with your specific date. Voila! You've got yourself a one-liner to fetch all users registered on that precious day.

Working with date ranges

To handle date ranges, SQLAlchemy's between method works wonders. This code snippet fetches all users created in the prophetic month of January 2023:

# We bagged a lot of users in Jan 2023, but who exactly were they?
users = session.query(User).filter(
    User.created_at.between(date(2023, 1, 1), date(2023, 1, 31))
).all()

Ensure your date format harmonizes with that in your database to avert nasty errors.

Dealing with time component

When you want strictly date, not datetime, func.date is your best comrade. That pesky time component gets kicked to the curb just like that:

from sqlalchemy import func

# Time isn't of essence here, we just need the date!
users = session.query(User).filter(
    func.date(User.created_at) == date(2023, 1, 1)
).all()

Complex queries with filter chaining

For complex queries requiring multiple conditions, chaining filters have your back! Sequence your filters for the win:

from sqlalchemy import and_

# The double whammy! Count me in if the date is within Jan 2023!
users = session.query(User).filter(
    and_(
        User.created_at >= date(2023, 1, 1),
        User.created_at <= date(2023, 1, 31)
    )
).all()

To ensure you're nailing the right rows, print your SQL queries. They're your roadmap to your final query treasure:

# Telling it as it is: The SQL style!
print(users)

Working with age intervals

timedelta, the handy function for age interval calculations, subtracts years giving you a glimpse of the past:

from datetime import timedelta

# Time machine mode: On. Let's go back 20 and 30 years in time!
start_date = date.today() - timedelta(days=365*20)  # 20 years ago
end_date = date.today() - timedelta(days=365*30)    # 30 years ago
# Who was there then? Let's find them!
users = session.query(User).filter(
    User.birth_date.between(end_date, start_date)
).all()

Prettifying output with Flask-Marshmallow

Flask-Marshmallow is your BFF when it comes to prettifying output of your queries, especially when working with Flask:

from flask import jsonify
from your_application import ma

class UserSchema(ma.Schema):
    class Meta:
        fields = ('id', 'username', 'created_at')

# Dressed to impress: Prettify me!
user_schema = UserSchema()
users = session.query(User).filter(User.created_at == date(2023, 1, 1)).all()
# Let's see the magic!
output = user_schema.dump(users, many=True)
return jsonify(output)