选择过去六小时的值

First thing, my date format for the field is TIMESTAMP

首先,我的日期格式是TIMESTAMP

I have a table that consists of id, domain, hits, when.

我有一个表,包括id,domain,hits,when。

Now when I insert the hits into the database, i'm pulling from my analytics API every 10 minutes. So the content of this table is

现在,当我将命中数据插入数据库时,我每10分钟从我的分析API中提取一次。所以这个表的内容是

id=1, domain=test.com, hits=930, when=2011-11-22 16:00:22;

id=2, domain=test2.com, hits=134, when=2011-11-22 16:10:22;

etc

Once this fills up with data every 10 minutes for the last six hours, I want to find the number of hits for each domain for each hour. so:

一旦在过去的六个小时内每10分钟填满一次数据,我想找到每小时每个域的点击次数。所以:

There will be 6 rows per hour since it inserts every 10 minutes, how can I retrieve the total number of hits for each hour?

由于每10分钟插入一次,每小时将有6行,如何检索每小时的总点击次数?

If I want to pull my statistics at 1:39PM for the current hour, I should be able to pull all the hits from 1:10PM, 1:20PM, 1:30PM.

如果我想在当前时间下午1点39分统计我的统计数据,我应该能够从下午1:10,下午1点20分,下午1点半开始全部点击。

DO I need six separate mysql queries that take the current time and run it for each hour because I was be displaying it in a graph (1 hour ago, 2 hours ago, 3 hours ago, etc) or is there a simpler way to achieve this?

我是否需要六个单独的mysql查询,这些查询需要花费当前时间并且每小时运行一次,因为我是在图表中显示它(1小时前,2小时前,3小时前等)或者是否有更简单的方法来实现这个?

Please help me understand, thanks!

请帮我理解,谢谢!

1 个解决方案

#1

You could try

你可以试试

select domain, date_format(when, '%Y-%m-%d %I%p') hour, sum(hits)
from table group by domain, hour

EDIT: I changed the format from %H for 24 hour clock to %I%p for 12 hour clock with AM/PM, since it seems like that's what you want.

编辑:我将格式从%H改为24小时制到%I%p为12小时制AM / PM,因为看起来这就是你想要的。

EDIT2: If it's only the last six hours you want, grouped by how many hours ago the hits were, you might be better off with (added date_format to group by whole hour)

编辑2:如果只是你想要的最后六个小时,按照点击次数分组,你可能会更好(添加date_format到整个小时组)

select domain, hour(timediff(date_format(now(), '%Y-%m-%d %H'), 
    date_format(when, '%Y-%m-%d %H'))) hours, sum(hits)
from table
where hours < 7
group by domain, hours

#1