CODEWITHSUNDEEP
mysqlsql
matthias
matthias

Reputation: 2062

Select rows in mysql where difference between timestamp = 5 Minutes

I have a mysql table with a column "timestamp".

Now I want to select the elements with a difference of 5 minutes from each other.

Simplified example:

id   |    timestamp
===================
1    |    00:00
2    |    00:01
3    |    00:03
4    |    00:05
5    |    00:07
6    |    00:09
7    |    00:15

should return the ids 1, 4, 7

is this possible with a sql statement?

Upvotes: 1

Views: 644

Answers (2)

fancyPants
fancyPants

Reputation: 51888

Here's a solution, which should work no matter what's the first timestamp.

SELECT id, ts FROM (
SELECT 
IF(UNIX_TIMESTAMP(ts) = @prev OR UNIX_TIMESTAMP(ts) % 300 = @prev % 300, @mybool:=1, @mybool:=0) AS mybool,
IF(@mybool = 1, @prev:=UNIX_TIMESTAMP(ts), @prev:=@prev),
t.*
FROM Table2 t,
(SELECT @prev:=(SELECT MIN(UNIX_TIMESTAMP(ts)) FROM Table2 sqt), @mybool:=1) vars
ORDER BY id
) sq 
WHERE mybool = 1;

See it working live in an sqlfiddle.

If the first timestamp is '00:00' you can simply do 

SELECT * 
FROM Table1
WHERE UNIX_TIMESTAMP(ts) % 300 = 0
ORDER BY id;

but you have to work with UNIX_TIMESTAMP() to make it work. A timestamp is stored in database as a 32-bit integer, still you have to tell MySQL that you want to work with the integer representation.

Upvotes: 1

Niels Keurentjes
Niels Keurentjes

Reputation: 41958

select id from table where timestamp % 5 = 0

Precise syntax depends a bit on the datatype of the timestamp column but in general you should be able to do it with the modulo operator.

Upvotes: 0

Related Questions