One way to do this is to use a function within your database to convert a timestamp down to a basic time type, and then do comparison on the converted value. Here is an example using sqlite as the back end. Sqlite has a "time" function that can convert a datetime down to a time for you, so we "get at" that using sqlalchemy's func:
from sqlalchemy import create_engine, Column, Integer, DateTime from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:///test.sqlite') Session = sessionmaker(bind=engine) Base = declarative_base() class Test(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) timestamp = Column(DateTime, nullable=False) Base.metadata.create_all(bind=engine) # This is just sticking random timestamps into the database... import datetime import random session = Session() session.query(Test).delete() for i in range(100): d = random.randint(1, 30) h = random.randint(0, 23) m = random.randint(0, 59) test = Test() test.timestamp = datetime.datetime(2013, 8, d, h, m) session.add(test) session.commit() # Heres the important part. Pull in func from sqlalchemy import func # Say we want any timestamp in the db regardless of date where the time # is between 12:00 and 12:30 t1 = datetime.time(12, 00) t2 = datetime.time(12, 30) query = session.query(Test).filter(func.time(Test.timestamp).between(t1, t2)) for row in query.all(): print(row.timestamp) Regards, Jeff Peck -----Original Message----- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Warwick Prince Sent: Friday, August 30, 2013 8:01 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] How-to filter by the time part of a datetime field? > Hi Warwick, > > On 30/08/2013 14:38, Warwick Prince wrote: >> I'm sure there is a better way, but you could always filter using a date/time and supply the date part as well (i.e. today) so that you are comparing datetime to datetime. (Something like: select all rows where the datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-) > > Thanks for your suggestion, this could do the trick. > > However my rows are split over a lot of days and if I follow your advice I'll end with a lot of "between 2013-08-30 06:00:00 and 2013-08-30 11:00:00" (one for each day). This will certainly work as expected, but I'm looking for a more efficient way of doing this. > > > Thanks. > -- > Laurent Meunier <laur...@deltalima.net> > Ahh - I see. Oh well, I'm sure someone with infinitely better SQL skills with chime in shortly. :-) Warwick > -- > You received this message because you are subscribed to the Google Groups "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.