CODEWITHSUNDEEP
mysqlgroup-by
Vishal Kumar Verma
Vishal Kumar Verma

Reputation: 312

MYSQL Time Interval and Sum of Data according to the interval

I have a table which will log all the data based on one minute interval. Now I want to retrieve the records such that, if the time interval is given 2 minutes it should add the complete records with 2 minutes interval and show on screen.. Suppose the Table is:

Date | Time | Data

20-11-2011| 9:00 | 2

20-11-2011| 9:01 | 3

20-11-2011| 9:02 | 6

20-11-2011| 9:03 | 2

20-11-2011| 9:04 | 1

20-11-2011| 9:05 | 4

20-11-2011| 9:06 | 4

So, if the start date is selected as 20-11-2011 and start time as 9:01 and interval as 2 minutes, then the table should be:

Date | Time | Data

20-11-2011| 9:01 | 9

20-11-2011| 9:03 | 3

20-11-2011| 9:05 | 8

Kindly help me with this, I'm new to mysql and php

Here: Time is a Timestamp type..

Upvotes: 0

Views: 1153

Answers (1)

zerkms
zerkms

Reputation: 255155

This should work:

SELECT SUM(`data`), MIN(`time)
FROM tblname
WHERE `time` >= '9:01' AND `date` = '2011-11'20
GROUP BY FLOOR((TIME_TO_SEC(`time`) - TIME_TO_SEC('9:01')) / 60 / 2)

Upvotes: 2

Related Questions