计数mysql数据库中X间隔内的时间戳记的行 [英] Count rows in mysql database where timestamp within X interval

问题描述

我试图计算X个小时或X天内插入的数据库表中的行.我已经反复尝试，但是我总是得到空的设置响应.

I am trying to count the rows in a table in my database that were inserted within X hours or X days. I have tried repeatedly but I keep getting empty set responses.

我表中的start_stamp列的格式如下:2013-08-07 18:18:37

The start_stamp column in my table is formatted like this: 2013-08-07 18:18:37

我在以下方面尝试了多种变体: 从mytable中选择*，其中start_stamp = now()间隔-1天；

I have tried many variations on: select * from mytable where start_stamp = now() interval -1 day;

但都无济于事.有人可以告诉我我在做什么错吗?

But all to no avail. Can someone tell me what I am doing wrong?

推荐答案

而不是选择start_stamp等于 等于now() - 1day的行，您需要的行大于或等于该范围.此外，您的语法有点差. MySQL的日期算术使用column_value - INTERVAL <number> <period>，因此您需要:

Rather than selecting rows where start_stamp is equal to now() - 1day, you need rows where it is greater than or equal to that range. Additionally, your syntax is a little off. MySQL's date arithmetic uses column_value - INTERVAL <number> <period>, so you need:

SELECT COUNT(*) AS num_new_rows
FROM mytable
WHERE start_stamp >= NOW() - INTERVAL 1 DAY

同样，要获取 n 小时前，请使用INTERVAL n HOUR

Likewise to get n hours ago, use INTERVAL n HOUR

# Within 3 hours...
WHERE start_stamp >= NOW() - INTERVAL 3 HOUR

日期间隔算术的语法在

The syntax for date interval arithmetic is described in a small paragraph beneath the DATE_ADD() function reference in the official MySQL documentation.

这篇关于计数mysql数据库中X间隔内的时间戳记的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！