Since it is the primary key a simple "sort by diff ascending, fetch 1st row" might not be the fastest, though simple solution. A quick and dirty way could be to fetch a union of larger than and less than the given datetime, sorted ascending and descending and limited to 1st row, and then pick the one with the smaller diff from the 2.
Here's an example using Postgresql as backend and a test table with a year's worth of timestamps with 12s resolution:
sopython=> create table testi (key timestamp without time zone primary key);
CREATE TABLE
sopython=> insert into testi
select *
from generate_series(now() at time zone 'utc' - '1 year'::interval,
now() at time zone 'utc',
'12 seconds'::interval);
INSERT 0 2628001
and the Python:
In [29]: from sqlalchemy import union_all, case
In [30]: from sqlalchemy.orm import aliased
In [31]: the_time = datetime(2016, 5, 5, 10, 45, 55)
Create the union that fetches the closest values, wrapping the subqueries in a SELECT statement, so that it works in SQLite as well, for example.
In [32]: greater = session.query(Testi).filter(Testi.key > the_time).\
...: order_by(Testi.key.asc()).limit(1).subquery().select()
In [33]: lesser = session.query(Testi).filter(Testi.key <= the_time).\
...: order_by(Testi.key.desc()).limit(1).subquery().select()
In [34]: the_union = union_all(lesser, greater).alias()
Alias the model to the result of the union
In [35]: testi_alias = aliased(Testi, the_union)
Calculate the difference from the given datetime
In [36]: the_diff = testi_alias.key - the_time
or in SQLite
In [36]: the_diff = func.julianday(testi_alias.key) - func.julianday(the_time)
Fetch the closer of the 2. The case monstrosity is for getting the absolute value of the interval, in Postgresql. Other DBs require different solutions for the difference calculation and taking the absolute value. With SQLite simply func.abs(the_diff).
In [37]: session.query(testi_alias).\
...: order_by(case([(the_diff < timedelta(0), -the_diff)],
...: else_=the_diff)).\
...: first()
Out[37]: <sqlalchemy.ext.automap.testi at 0x7f096f837828>
In [38]: _.key
Out[38]: datetime.datetime(2016, 5, 5, 10, 45, 54, 855799)
While the simple solution of just ordering by diff and limiting ran in some 800ms on this machine, the above query finishes in about 70-100ms. If you double the data, the simple solution – relying on seq scan – doubles as well.
The union finds these two values from the table:
In [14]: session.query(testi_alias.key).all()
Out[14]:
[(datetime.datetime(2016, 5, 5, 10, 45, 54, 855799)),
(datetime.datetime(2016, 5, 5, 10, 46, 6, 855799))]
And finally, you can wrap it all up in a generic function:
def get_closest(session, cls, col, the_time):
greater = session.query(cls).filter(col > the_time).\
order_by(col.asc()).limit(1).subquery().select()
lesser = session.query(cls).filter(col <= the_time).\
order_by(col.desc()).limit(1).subquery().select()
the_union = union_all(lesser, greater).alias()
the_alias = aliased(cls, the_union)
the_diff = getattr(the_alias, col.name) - the_time
abs_diff = case([(the_diff < timedelta(0), -the_diff)],
else_=the_diff)
return session.query(the_alias).\
order_by(abs_diff.asc()).\
first()
get_closest(session, Testi, Testi.key, the_time)