How to Filter SQLAlchemy Users by Age Range Using Date Fields?

SQLAlchemy: Filtration of Date Fields

Querying Users Within a Specific Age Range

Given the model definition below:

class User(Base):
    ...
    birthday = Column(Date, index=True)   # Ex. '1987-01-17' in the database
    ...

The objective is to retrieve all users within an age range, such as those between 18 and 30 years old.

Correct Filter Implementation

To achieve the desired filtering, the following SQLAlchemy query can be employed:

query = DBSession.query(User).filter(
    User.birthday.between('1985-01-17', '1988-01-17')
)

Explanation:

• The between() operator is used to specify the lower and upper bounds of the date range.
• The date values are formatted as strings in the format 'YYYY-MM-DD'.

Alternative Filtering Methods

Aside from using the between() operator, there are other methods for filtering date fields:

# Filter by upper bound date
query = DBSession.query(User).filter(User.birthday <= '1988-01-17')
# Filter by lower bound date
query = DBSession.query(User).filter(User.birthday >= '1985-01-17')
# Filter by equality
query = DBSession.query(User).filter(User.birthday == '1987-01-17')

The above is the detailed content of How to Filter SQLAlchemy Users by Age Range Using Date Fields?. For more information, please follow other related articles on the PHP Chinese website!