How-to filter by the time part of a datetime field?

[Laurent Meunier]

Hi,

I've a model with a DateTime column and I need to select rows where the
time part of this column is between two hours (something like: select
all rows where the date is between 6:00 and 11:00).

I need the date information, rows must be deleted after a couple of days.

I don't know how-to extract the time part of my DateTime field in
".filter()". Should I add a Time column ? Or is it possible to use only
the DateTime column?


Thanks.
--
Laurent Meunier <lau...@deltalima.net>

[Warwick Prince]

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.


Hi Laurent

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) :-)

Cheers
Warwick

[Laurent Meunier]

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.

[Warwick Prince]

Ahh - I see. Oh well, I'm sure someone with infinitely better SQL skills with chime in shortly. :-)

Warwick

[Jeff Peck]

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

[laurent+s...@deltalima.net]

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 :)

[Jonathan Vanasco]

In addition to using `func.*` methods, there's also the `extract` method that works (in most databases) specifically on datetime objects.  

sqlalchemy.sql.expression.extract(field, expr)

in any event, the general approach is the same -- the query should tell the database to turn a datetime into just a time, and compare that value to your filter.