SQLAlchemy: how to filter date field?

Question

Here is model:

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

I want to filter between two dates, for example to choose all users in interval 18-30 years.

How to implement it with SQLAlchemy?

I think of:

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

# means age >= 24 and age <= 27

I know this is not correct, but how to do correct?

score 252 · Accepted Answer · edited Apr 20 '23 at 18:44

252

In fact, your query is right except for the typo: your filter is excluding all records: you should change the <= for >= and vice versa:

qry = DBSession.query(User).filter(
        and_(User.birthday <= '1988-01-17', User.birthday >= '1985-01-17'))
# or same:
qry = DBSession.query(User).filter(User.birthday <= '1988-01-17').\
        filter(User.birthday >= '1985-01-17')

Also you can use between:

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

edited Apr 20 '23 at 18:44

ggorlen

44,755
7
76
106

answered Jan 17 '12 at 16:54

van

74,297
13
168
171

43

Btw, instead of `'1985-01-17'`, you can also use `datetime.date(1985, 1, 17)` - may be easier to get at or work with in some environments. – tossbyte Dec 22 '15 at 22:27
5

@rippleslash: you are right, and ideally one should use the proper data type for parameters. However all databases understand also the ISO 8601 format for dates, which also happens to be lexicographical order. For this reason for simple examples i generally use ISO formatted dates - easier to read. – van Dec 24 '15 at 03:12
@van in the between example are the dates inclusive? – rohitwtbs Aug 12 '21 at 09:46
@rohitwtbs: sqlalchemy will simply translate to the database implementation of `BETWEEN` operator, so the answer will be RDBMS dependent. but i do believe it is inclusive on all all modern RDBMS – van Aug 20 '21 at 19:08

score 13 · Answer 2 · answered Feb 27 '19 at 08:46

13

if you want to get the whole period:

    from sqlalchemy import and_, func

    query = DBSession.query(User).filter(and_(func.date(User.birthday) >= '1985-01-17'),\
                                              func.date(User.birthday) <= '1988-01-17'))

That means range: 1985-01-17 00:00 - 1988-01-17 23:59

answered Feb 27 '19 at 08:46

MrNinjamannn

631
7
11

5

**DANGER:** although this might be obvious to some - this ONLY works because the `func.date` does *CAST* on the column which **removes the time from equation** => this does *NOT* mean range with the **time!** This only works when time is NOT in the column - you must CAST it to Date like this, or make the column Date, once it's DateTime or timestamp - it's usually finished with 00:00 (both MySQL & PostgreSQL do this). More generic solution is not to cast, but to set the date you are sending to it's .endOfDay() so you actually send `1988-01-17 23:59:59` to the database compare :) – jave.web Jun 15 '20 at 22:14

score 7 · Answer 3 · edited Jun 01 '17 at 12:01

from app import SQLAlchemyDB as db

Chance.query.filter(Chance.repo_id==repo_id, 
                    Chance.status=="1", 
                    db.func.date(Chance.apply_time)<=end, 
                    db.func.date(Chance.apply_time)>=start).count()

it is equal to:

select
   count(id)
from
   Chance
where
   repo_id=:repo_id 
   and status='1'
   and date(apple_time) <= end
   and date(apple_time) >= start

wish can help you.

ikreb · Answer 4 · 2021-10-07T08:12:38.513

Here is a version which works with the latest Flask version and uses flask-marshmallow.

from datetime import date, timedelta

from flask import jsonify

from app import db, ma
from app.models import User

from . import main

class UserSchema(ma.Schema):
    class Meta:
        fields = ('forename', 'surname', 'birthday', ...)


@main.route('/', methods=('GET',))
def get_users():

    start_range = date.today() + timedelta(years=-30)
    end_range = date.today() + timedelta(years=-18)

    users = db.session.query(User).filter(User.birthday.between(start_range, end_range)).all()

    users_schema = UserSchema(many=True)

    return jsonify(users_schema.dump(users))

I think you mistyped `date.today() + timedelta(years=-18)` should be named `end_range` — lno23, Oct 06 '21 at 19:05

SQLAlchemy: how to filter date field?

4 Answers4

Linked

Related