通过基于分钟跳过记录从表中获取数据

问题描述

我在 mysql 中有 tracklog 表，其中包含各种字段，包括日期时间字段.

I have tracklog table in mysql which has various fields including datetime field.

tracklog_data

tracklog_data

id  pos_datetime
1   2015-05-05 12:08:50 
2   2015-05-05 12:08:30 
3   2015-05-05 12:07:40
4   2015-05-05 12:07:20
5   2015-05-05 12:07:10  
6   2015-05-05 12:06:05
7   2015-05-05 12:06:01
8   2015-05-05 12:05:30
9   2015-05-05 12:05:10
10  2015-05-05 12:04:30
11  2015-05-05 12:04:20
12  2015-05-05 12:03:50 
13  2015-05-05 12:03:20
14  2015-05-05 12:02:50
15  2015-05-05 12:02:40
16  2015-05-05 12:02:10
17  2015-05-05 12:01:40
18  2015-05-05 12:01:10
19  2015-05-05 12:01:05
20  2015-05-05 12:00:30
21  2015-05-05 11:59:30

我想要每分钟的数据，这意味着如果有两个记录具有相同的分钟，那么只有第一个应该跳过具有相同小时和分钟的所有其他记录.然后它也应该跳过接下来的两分钟记录并移动到第三分钟并在

I want data minute wise that means if there are two records having same minute then only first one should come skipping all the other records having same hour and minute. Then it should also skip next two minutes records and move to third minute and do the same further on

所以例如我需要的时间限制是 2 分钟假设

so for example I need timelimit to skip is 2 minute suppose

id  pos_datetime
1   2015-05-05 12:08:30
8   2015-05-05 12:05:30
14  2015-05-05 12:02:50
21  2015-05-05 11:59:30

请帮助我会很高兴..

推荐答案

SELECT id, pos_datetime
FROM mytable 
WHERE id IN (
SELECT MIN(id)
FROM mytable 
GROUP BY 
ROUND(UNIX_TIMESTAMP(pos_datetime)/(1 * 60)))

此处演示

这篇关于通过基于分钟跳过记录从表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！