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

Question

My table has a datetime column that records when the row was updated; call it col_datetime. I need to get the row with the latest datetime in col_datetime and all other records that are within a minute of that record and have the 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 my case, I'd need the query to return rows 1 and 3 (even though row 2 is within 1 minute of record #1, it is not the same minute. Likewise, row #4 has the same minute but is a year earlier).

My attempt in mysql only returns row #1 (though I need a solution for SQLAlchemy):

SELECT * FROM (SELECT * FROM `mytable`
        ORDER BY col_datetime DESC LIMIT 1) as sub 
        WHERE col_datetime >= sub.col_datetime - INTERVAL 1 MINUTE 
        AND EXTRACT(MINUTE FROM col_datetime) = EXTRACT(MINUTE FROM sub.col_datetime)

I appreciate the help!

Answer 1

This sql query will return the correct data:

select * from 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.* 
  from foo,   
  (select convert( DATE_FORMAT(max(col_date), '%Y-%m-%d %H:%i:00'), DATETIME) as minute_base from foo) b   
where foo.col_date >= b.minute_base and
      foo.col_date < b.minute_base + INTERVAL 1 MINUTE;

Sqlalchemy does not support server side functions like DATE_FORMAT or INTERVAL out of the box. To do it on the server side create custom sql constructs (SQLAlchemy datetime operations on server side).

On the client side with two 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 be overkill.