SQLAlchemy: how to filter date field?
138,328
Solution 1
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'))
Solution 2
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
Solution 3
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.
Author by
Vitalii Ponomar
Updated on October 07, 2021Comments
-
Vitalii Ponomar over 2 years
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?
-
tossbyte over 8 yearsBtw, instead of
'1985-01-17'
, you can also usedatetime.date(1985, 1, 17)
- may be easier to get at or work with in some environments. -
van over 8 years@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.
-
jave.web almost 4 yearsDANGER: 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 send1988-01-17 23:59:59
to the database compare :) -
rohitwtbs almost 3 years@van in the between example are the dates inclusive?
-
van almost 3 years@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 -
lno23 over 2 yearsI think you mistyped
date.today() + timedelta(years=-18)
should be namedend_range