SQLAlchemy——如何获得与最新记录在一分钟和同一分钟内的所有记录?

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:

我的表有一个datetime列，记录行更新的时间;称之为col_datetime。我需要获取col_datetime中最新的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

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

在我的例子中，我需要查询返回第1行和第3行(尽管第2行在记录#1的1分钟内，但这不是同一分钟。同样，第4行也有同样的时间，但比第4行早了一年)。

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

我在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)

I appreciate the help!

我很感谢帮助!

1 个解决方案

#1

This sql query will return the correct data:

这个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 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).

Sqlalchemy不支持服务器端函数，如DATE_FORMAT或INTERVAL out of the box。要在服务器端执行此操作，请创建自定义sql构造(服务器端SQLAlchemy datetime operations)。

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.

PS max_minute可能有些过份。

#1