python - SQLAlchemy - how to get all records that are within 1 minute and the same minute of the latest record? -

my table has datetime column records when row updated; call col_datetime. need row latest datetime in col_datetime , other records within minute of record , have same minute. example:

pk  | first  | col_datetime 1     dave     2014-03-23 8:23:57 2     dan      2014-03-23 8:22:59 3     teresa   2014-03-23 8:23:01  4     marge    2013-03-23 8:23:08 

in case, i'd need query return rows 1 , 3 (even though row 2 within 1 minute of record #1, not same minute. likewise, row #4 has same minute year earlier).

my attempt in mysql returns row #1 (though need solution sqlalchemy):

select * (select * `mytable`         order col_datetime desc limit 1) sub          col_datetime >= sub.col_datetime - interval 1 minute          , extract(minute col_datetime) = extract(minute sub.col_datetime) 

i appreciate help!

this sql query return correct data:

select * foo; +----+--------+---------------------+ | id | name   | col_date            | +----+--------+---------------------+ |  1 | bob    | 2014-04-05 19:57:53 | |  2 | robert | 2014-04-05 19:58:15 | |  3 | fred   | 2014-04-05 19:58:25 | |  4 | frank  | 2014-04-05 19:58:48 | +----+--------+---------------------+  select foo.*    foo,      (select convert( date_format(max(col_date), '%y-%m-%d %h:%i:00'), datetime) minute_base foo) b    foo.col_date >= b.minute_base ,       foo.col_date < b.minute_base + interval 1 minute; 

sqlalchemy not support server side functions date_format or interval out of box. on server side create custom sql constructs (sqlalchemy datetime operations on server side).

on client side 2 queries:

minute = conn.execute(select([func.max(foo.c.col_date, type=datetime)])).scalar().replace(second=0) max_minute = minute + datetime.timedelta(minutes=1) conn.execute(select([foo]).\ ...:     where(foo.c.col_date >= minute).\ ...:     where(foo.c.col_date < max_minute)).fetchall()  [(2, 'robert', datetime.datetime(2014, 4, 5, 19, 58, 15)),  (3, 'fred', datetime.datetime(2014, 4, 5, 19, 58, 25)),  (4, 'frank', datetime.datetime(2014, 4, 5, 19, 58, 48))] 

ps max_minute may overkill.