Hello: I've two tables having a column 'date'. One holds (title, date) and also the other holds (date, p1, p2). Given a title, I wish to make use of the date in table 1 to question p1 and p2 from table two the match should happen if date in table the first is within two seconds of date in table two.

How will you make this happen using SQLAlchemy?

I have attempted (unsuccessfully) to make use of the between operator 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 ideas?

Edit: I have handled to resolve the issue while using 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

Therefore the comparison can be achieved, but I am unsure the approach is efficient.

Allow me to first explain why that which you attempted does not work. SQLAlchemy is simply a convenient method to write SQL queries, all of the querying is nevertheless happening around the remote side. SQLAlchemy posts are special objects whose __eq__, __gt__ etc techniques are overwritten to come back not True or False, but other special objects, which remember that which was the item these were in comparison to and may generate appropriate SQL claims later. Exactly the same is perfect for adding etc: The custom __add__, __sub__ method doesn't return several or perhaps a concatenated string but additionally this kind of object, that creates an sql statement. You are able to compare / add these to strings, integers etc, other posts, choose claims, mysql function calls etc, although not to special python objects like timedeltas. (Simplified, and most likely technically not 100% correct ) )

What exactly you should do is:

  • Result in the values within the database integers, eg unix timestamps. This way your between query works (with 2 rather than the delta)
  • Use database-side functions to transform the datetime format date to some unix timestamp after which perform the comparison.

UPDATE: I have performed around just a little with this, and in some way it will work, there even is definitely an [cde] data type. However a minimum of here it doesn't work correctly:

MySQL:

Interval

SQLite:

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

I'm not sure why the first fails on MySQL and why it returns floats. The SQLite errors appear to occur because SQLite does not have a DATETIME data type and SQLAlchemy stores it as a string.

You will need to adjust that the little, maybe you'll discover a way that actually works - however i want to stay really dbms independent the integer method would be the only achievable way.