Filter objects within two seconds of one another using SQLAlchemy

Question

I have two tables with a column 'date'. One holds (name, date) and the other holds (date, p1, p2). Given a name, I want to use the date in table 1 to query p1 and p2 from table two; the match should happen if date in table one is within two seconds of date in table two.

How can you accomplish this using SQLAlchemy?

I've tried (unsuccessfully) to use the between operator and with a clause like:

td = datetime.timedelta(seconds=2)
q = session.query(table1, table2).filter(table1.name=='my_name').\
    filter(between(table1.date, table2.date - td, table2.date + td))

Any thoughts?

Edit: I've managed to solve the problem using the following approach:

from sqlalchemy.sql import between
import datetime
# [all other relevant imports]

td = datetime.timedelta(seconds=2)
t1_entry = session.query(table_1).filter(table_1.name == 'the_name').first()
if t1_entry is not None:
 tmin = t1_entry.date - td
 tmax = t1_entry.date + td
 t2_entry = session.query(table_2).filter(between(table_2.date, tmin, tmax)).first()
 return (t1_entry, t2_entry)
return None

So the comparison can be done, but I'm not sure the approach is efficient.

Answer 1

Let me first explain why what you tried doesn't work. SQLAlchemy is just a convenient way to write SQL queries, all the querying is nonetheless happening on the remote side. SQLAlchemy columns are special objects whose __eq__, __gt__ etc methods are overwritten to return not True or False, but other special objects, which remember what was the object they were compared to and can generate appropriate SQL statements later. The same is for adding etc: The custom __add__, __sub__ method does not return a number or a concatenated string but also such an object, that generates an sql statement. You can compare / add them to strings, integers etc, other columns, select statements, mysql function calls etc, but not to special python objects like timedeltas. (Simplified, and probably technically not 100% correct ;) )

So what you can do is:

• Make the values in the database integers, eg unix timestamps. That way your between query will work (with 2 instead of the delta)
• Use database-side functions to convert the datetime format date to a unix timestamp and then do the comparison.

UPDATE: I've played around a little with that, and somehow it does work, there even is an Interval data type. However at least here it does not work properly:

MySQL:

>>> db.session.execute(db.select([User.date_joined, User.date_joined + timedelta(seconds=2)], limit=1)).fetchall()
[(datetime.datetime(2009, 7, 10, 20, 47, 33), 20090710204733.0)]
>>> db.session.execute(db.select([User.date_joined, User.date_joined + 2], limit=1)).fetchall()
[(datetime.datetime(2009, 7, 10, 20, 47, 33), 20090710204735.0)]
>>> db.session.execute(db.select([User.date_joined+0, User.date_joined + 2], limit=1)).fetchall()
[(20090710204733.0, 20090710204735.0)]

SQLite:

>>> db.session.execute(db.select([User.date_joined, User.date_joined + timedelta(seconds=2)], limit=1)).fetchall()
TypeError: expected string or buffer
>>> db.session.execute(db.select([User.date_joined, User.date_joined + 2], limit=1)).fetchall()
[(datetime.datetime(2010, 5, 28, 23, 8, 22, 476708), 2012)]
>>> db.session.execute(db.select([User.date_joined+0, User.date_joined + 2], limit=1)).fetchall()
[(2010, 2012)]

I don't know why the first one fails on MySQL and why it returns floats. The SQLite errors seem to happen because SQLite does not have a DATETIME data type and SQLAlchemy stores it as a string.

You'll have to play around with that a little, maybe you'll find a way that works - but I think to stay really dbms independent the integer method will be the only feasible way.

Answer 2

The approach is to convert the dates to unix timestamps.

In a recent code I used the following lines successfully:

from sqlalchemy.sql import func
...
q = q.join(q2, func.abs(func.unix_timestamp(rssi1.datetime)-func.unix_timestamp(q2.c.datetime)) <=2 )

Note, however, that func.xxx simply copies xxx to the query as a string, so the database has to support function xxx. This example is for MySQL.