DB，如何根据时间和特定间隔选择数据

I have a table in my database, my program will insert data to that table in every 10 mins.

我的数据库中有一个表,我的程序将每隔10分钟将数据插入该表。

The table has a field recording the insert date and time.

该表有一个记录插入日期和时间的字段。

Now I want to retrieve those data, but I don't want hundreds of data comes out.

现在我想检索这些数据,但我不希望有数百个数据出来。

I want to get 1 records from every half hour based on insert time stamp (so less than 50 in total of a day).

我想根据插入时间戳每半小时获得1条记录(所以每天总共少于50条记录)。

For that 1 record, it can be either random pick or average from each interval. Sorry for the ambiguit, cuz I just wanna figure out the way to select from intervals

对于该1记录,它可以是随机选择或每个间隔的平均值。抱歉,这个ambiguit,因为我只是想弄清楚从间隔中选择的方式

Let say,

Table name: network_speed  
----------------------------------

ID.  .......  Speed   .........     Insert_time

1    .......    10    .........         10:02am......
2    .......    12    .........         10:12am......
...
...
...
123   .......   17     ........        9:23am........

To get them all but out put must be average of each half hour record

除了输出外,它们必须是每半小时记录的平均值

How can I write a query to achieve this?

如何编写查询来实现此目的?

2 个解决方案

#1

Here is a query that calculates half hour intervals on a specific day ( 2013-09-04).

以下是在特定日期(2013-09-04)计算半小时间隔的查询。

SELECT ID, Speed, Insert_time,
ROUND(TIMESTAMPDIFF(MINUTE, '2013-09-04', Insert_time)/48) AS 'interval'
FROM network_speed
WHERE DATE(Insert_time) = '2013-09-04';

Use that in a nested query to get stats on the records in the intervals.

在嵌套查询中使用它来获取间隔中记录的统计信息。

SELECT IT.interval, COUNT(ID), MIN(Insert_time), MAX(Insert_time), AVG(Speed)
FROM
    (SELECT ID, Speed, Insert_time,
     ROUND(TIMESTAMPDIFF(MINUTE, '2013-09-04', Insert_time)/48) AS 'interval'
     FROM network_speed
     WHERE DATE(Insert_time) = '2013-09-04') AS IT
GROUP BY IT.interval;

Here it is used to get the first record in each interval.

在这里它用于获取每个间隔中的第一条记录。

SELECT NS.*
FROM
    (SELECT IT.interval, MIN(ID) AS 'first_id'
     FROM
        (SELECT ID, Speed, Insert_time,
         ROUND(TIMESTAMPDIFF(MINUTE, '2013-09-04', Insert_time)/48) AS 'interval'
         FROM network_speed
         WHERE DATE(Insert_time) = '2013-09-04') AS IT
     GROUP BY IT.interval) AS MI,
     network_speed AS NS
WHERE MI.first_id = NS.ID;

Hope that helps.

希望有所帮助。

#2

Is this what you need?

这是你需要的吗?

   SELECT HOUR(ts) as hr, fld1, fld2 from tbl group by hr

This query selects only hour from the timestamp and then groups the result based on the hour field so you get 1 row for each hour

此查询仅从时间戳中选择小时,然后根据小时字段对结果进行分组,以便每小时获得1行

#1