CODEWITHSUNDEEP
mysql
eLemEnt
eLemEnt

Reputation: 1801

Fetch data from table by skipping records based on date time interval

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

tracklog_data

id  pos_datetime
1   2015-05-05 12:04:50 
2   2015-05-05 12:04:30 
3   2015-05-05 12:04:10
4   2015-05-05 12:03:40
5   2015-05-05 12:03:20  
6   2015-05-05 12:03:05
7   2015-05-05 12:03:01
8   2015-05-05 12:02:42
9   2015-05-05 12:02:20
10  2015-05-05 12:01:30

Now i want to fetch this records but what I want is that it should fetch only one record for particular minute. For example in give table it should first take id with 1 then it should skip all the next records having same minute so it should skip record with id 2 3 since its hour and minute matching with id 1 i.e. 12 hour 4 minute. Then it should fetch record with id 4 since its minute is different and skip all next record with 12 hour and 3 minute and move to 12 hour and 2 minute.

so in the end output should be

id  pos_datetime
1   2015-05-05 12:04:50
4   2015-05-05 12:03:40
8   2015-05-05 12:02:42
10  2015-05-05 12:01:30

Please help me guys...Thank you

Upvotes: 0

Views: 223

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT id, pos_datetime, val
FROM mytable 
WHERE id IN (
   SELECT MIN(id)
   FROM mytable
   GROUP BY DATE(pos_datetime), 
            HOUR(pos_datetime), 
            MINUTE(pos_datetime) )

GROUP BY clause of the sub-query places inside the same group all records having the same Date, Hour and Minute. MIN(id) simply returns the record having the minimum id value per group.

Demo here

You can alternatively perform INNER JOIN with the derived table of the sub-query:

SELECT m.id, pos_datetime, val
FROM mytable AS m
INNER JOIN (SELECT MIN(id) AS id
            FROM mytable
            GROUP BY DATE(pos_datetime), 
                     HOUR(pos_datetime), 
                     MINUTE(pos_datetime)) t
ON m.id = t.id

val field has been added just for demonstrational purposes.

Demo here

Upvotes: 1

Related Questions