filtering time range in datetime field in sqlalchemy

Question

I got a weird problem when I was doing the time range filtering in sqlalchemy. There is a datetime column in Event table called startDate. I was writing the query to return all the events that have the starting time between 6pm and 11:59pm (doesn't matter which date).

I have the following code,

from sqlalchemy import and_, Date, Time, cast
import datetime
startingTime = datetime.time(18, 0, 0)
endingTime = datetime.time(23, 59, 59)

If I run,

results = session.query(Event).filter(and_(cast(Event.startDate, Time)>=startingTime, cast(Event.startDate, Time)<=endingTime).all()

I got no result (for sure there are events in this range).

But if I run,

results = session.query(Event).filter(cast(Event.startDate, Time)>=endingTime).all()

It returns all rows in the table.

If I filter by dates,

results = session.query(Event).filter(and_(cast(Event.startDate, Date)>=one_date, cast(Event.startDate, Date)<=another_date).all()

results are correct!

Let's say the startDate of Event_A is 2013-11-27T19:30:00, and

result = session.query(cast(Event.startDate, Time)).filter(Event.eventName=='Event_A').one()[0]
result >= startingTime ----> returns True
result <= endingTime ----> returns True

Any help would be appreciated.

Welcome to StackOverflow! Please make sure that if you're including code that you use the builtin formatting features of StackOverflow. You can highlight your code and click the on {} button in the text editor toolbar to do this. — Mark Hildreth
– Mark Hildreth, Commented Nov 27, 2013 at 23:51
@CharlsGao: it would be very useful if for each SA query that you showed you would also add the resulting SQL statement. You can see this by enabling logging via passing setting engine.echo=True. — van
– van, Commented Nov 28, 2013 at 19:52

Charls Gao · Accepted Answer · 2013-11-29 21:46:44Z

1

Actually I guess it is because I am using sql server 2005, and DATE and TIME object will only be supported by the version after sql server 2008.

I found an alternative way to accomplish this task by using func.convert:

session.query(TableName).filter(func.convert(func.VARCHAR(8), TableName.datetimefield, 8) >= datetime.time(0,0)).all()

answered Nov 29, 2013 at 21:46

Charls Gao

1233 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jpwagner · Accepted Answer · 2013-11-27 23:54:02Z

0

i cannot replicate this behavior.

to debug, do this in steps:

session.query(TableName).filter(cast(TableName.some_datetimefield, Time) > '00:00:00').all() 
# > all
session.query(TableName).filter(cast(TableName.some_datetimefield, Time) > '23:59:59').all()
# > []
session.query(TableName).filter(cast(TableName.some_datetimefield, Time) < '23:59:59').all()
# > all
session.query(TableName).filter(cast(TableName.some_datetimefield, Time) > datetime.time(0,0)).all()
# > all
session.query(TableName).filter(cast(TableName.some_datetimefield, Time) > datetime.time(23,59,59)).all()
# > []
session.query(TableName).filter(cast(TableName.some_datetimefield, Time) < datetime.time(23,59,59)).all()
# > all
session.query(TableName).filter(cast(TableName.some_datetimefield, Time) < datetime.time(23,59,59)).filter(cast(TableName.some_datetimefield, Time) > datetime.time(0,0)).all()
# > all
session.query(TableName).filter(and_(cast(TableName.some_datetimefield, Time) < datetime.time(23,59,59), cast(TableName.some_datetimefield, Time) > datetime.time(0,0)).all()
# > all

answered Nov 27, 2013 at 23:54

jpwagner

5632 silver badges8 bronze badges

3 Comments

Charls Gao Over a year ago

I ran session.query(TableName).filter(cast(TableName.some_datetimefield, Time) > datetime.time(0,0)).all() returns all but session.query(TableName).filter(cast(TableName.some_datetimefield, Time) < datetime.time(23,59,59)).all() returns []

jpwagner Over a year ago

did session.query(TableName).filter(cast(TableName.some_datetimefield, Time) < '23:59:59').all() return everything?

Charls Gao Over a year ago

well, session.query(TableName).filter(cast(TableName.some_datetimefield, Time) < '23:59:59').all() returns empty result ([]), but session.query(TableName).filter(cast(TableName.some_datetimefield, Time) > '00:00:00').all() is actually returning everything.

Collectives™ on Stack Overflow

filtering time range in datetime field in sqlalchemy

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related