With specific dates AND times in a database column, how do I query for all records on a specific day?

Question

Convoluted title. I am using python sqlalchemy with a postgresql database.

I have a database that stores Events, and each event record has a starttime and a endtime column. Both of the aforementioned columns are in this format:

2015-06-22 13:00:00

How can I therefore query the database for all Events from a specific day. Thereby ignoring the "time" part of the starttime and endtime columns?

For example I tried:

query = Event.query.filter(Event.starttime=='2015-06-22', Event.endtime=='2015-06-22').all()

However this came up with 0 results, I am assuming perhaps this is because the absence of hh/mm/ss makes the query 'assume' that I mean 00:00:00.

I also tried

query = Event.query.filter(Event.starttime>='2015-06-22', Event.endtime<='2015-06-22').all()

But this came up with 0 results as well, I assume for the same reason as the previous.

Any help would be appreciated!

This is the query that gets sent to the DB:

SELECT events.id AS events_id, events.summary AS events_summary, events.starttime AS events_starttime, events.endtime AS events_endtime, events.parentcal AS events_parentcal, events.userid AS events_userid 
FROM events 
WHERE events.starttime >= :starttime_1 AND events.endtime < :endtime_1

It would be most helpful to log the query that actually gets sent to the db, see here http://stackoverflow.com/questions/2950385/debugging-displaying-sql-command-sent-to-the-db-by-sqlalchemy how to get these logs. — fvu, Jun 24 '15 at 08:58
Try to use `.filter(Event.starttime>='2015-06-22', Event.endtime<'2015-06-23')`. — pavel_form, Jun 24 '15 at 13:34
Columns are defined in my Models.py file as: `starttime = db.Column(db.DateTime, nullable=False)` `endtime = db.Column(db.DateTime, nullable=False)` — k4kuz0, Jun 25 '15 at 07:38

score 0 · Accepted Answer · answered Jun 25 '15 at 21:20

Try using actual datetime objects. SQLAlchemy can interpret them.

from datetime import datetime

starttime = datetime.strptime('2015-06-22', '%Y-%m-%d')
endtime = datetime.strptime('2015-06-23', '%Y-%m-%d')
query = Event.query.filter(Event.starttime>=starttime, Event.endtime<endtime).all()

For more information on parsing datetime strings check out the docs

With specific dates AND times in a database column, how do I query for all records on a specific day?

1 Answers1