Take data every 2 seconds MySql

Question

I would like to get the data for every 2 seconds and get the average data if they have same time.

For example :

Number      value        Time
[ 1]        [20.1000]    '2014-04-07 17:11:04'    
[ 2]        [20.2000]    '2014-04-07 17:11:05'    
[ 3]        [20.3000]    '2014-04-07 17:11:05'    
[ 4]        [20.4000]    '2014-04-07 17:11:06'    
[ 5]        [20.5000]    '2014-04-07 17:11:06'  

The results should be like this. :

Number      Value        Time
[ 1]        [20.1000]    '2014-04-07 17:11:04'   
[ 4]        [20.4500]    '2014-04-07 17:11:06'   

I just tried to get data every 2 second without averaging it using..

SELECT number,value,time FROM testdata WHERE time BETWEEN 17:00:00 AND 18.00:00
GROUP BY second(time) DIV 2

But, it did not give me good results.

Thank you.

Answer 1

Without testing, something like this should do it:-

SELECT `Time`, MIN(Number), AVG(value)
FROM testdata 
WHERE TIME(`Time`) BETWEEN '17:00:00' AND '18.00:00'
AND MOD(UNIX_TIMESTAMP(`Time`), 2) = 0
GROUP BY `Time`