How to filter specific row in mysql table?

Question

Assuming I have a table that have a column "time" store the date stamp every five minutes , just like this :

time
20120701 00:00:00
20120701 00:05:00
20120701 00:10:00
20120701 00:15:00
....

but interval between the rows sometimes are longer than 5 minites, like

20120703 00:00:00
20120703 00:15:00

What I want to do is to find out all these time stamps that the interval between them is longer than 5 minutes.

Is that possible to find out the result with just one sql sentence.

Thanks for kind help!

I doubt that this is doable in SQL in any sane manner. – Jon Jul 12 '12 at 09:13 — Jon, Jul 12 '12 at 09:13

score 0 · Answer 1 · answered Jul 12 '12 at 09:23

0

Try this

select time from table where time not in
(
select t1.time from table as t1 inner join table as t2 on t1.time=t2.time-interval 5 minute
)

answered Jul 12 '12 at 09:23

Madhivanan

13,470
1
24
29

score 0 · Answer 2 · edited May 23 '17 at 10:09

0

You can use a rank to compare the previous row with the 'current' one and check if it's greater than 5 minutes. Have a look here to see how to create a rank: Rank function in MySQL

edited May 23 '17 at 10:09

Community

1
1

answered Jul 12 '12 at 09:26

stb

3,405
2
17
24

How to filter specific row in mysql table?

2 Answers2