SQLAlchemy - 如何获取与最新记录同一分钟内的所有记录？

Question

我的表里有一个日期时间的列，记录了每一行数据最后更新的时间，叫做 col_datetime。我需要找到 col_datetime 中最新的时间那一行，以及所有在这个时间的一分钟内，并且在同一分钟的其他记录。举个例子：

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

在我的例子中，我希望查询能返回第1行和第3行（虽然第2行在第1行的1分钟内，但它不是同一分钟的记录。同样，第4行虽然在同一分钟，但时间早了一年）。

我在 MySQL 中的尝试只返回了第1行（不过我需要一个适用于 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)

谢谢大家的帮助！

Answer 1

这个SQL查询会返回正确的数据：

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 默认不支持像 DATE_FORMAT 或 INTERVAL 这样的服务器端函数。如果想在服务器端使用这些功能，需要创建自定义的SQL结构（关于SQLAlchemy在服务器端的日期时间操作）。

如果在客户端使用两个查询的话：

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

顺便说一下，max_minute 可能有点过于复杂了。