Wonderfull! Exactly what I was looking for. Thank you Jeff.
I was searching in the sqlalchemy documentation for a "time" function,
but haven't found one. In fact, you can use any function supported by
the database directly with `func`. So I can use `func.time` and this
will use the TIME function of sqlite ... awesome! I've learnt something
new today :)
On 30/08/2013 15:53, Jeff Peck wrote:
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.
