Is there a way for GROUP BY time interval in this table?

Question

I have a table like this:

DateTime A 10:00:01 2 10:00:07 4 10:00:10 2 10:00:17 1 10:00:18 3 

Is it possible to create a query that returns me the average value of A every 10 seconds? In this case, the result will be:

 3 (4+2)/2 2 (2+1+3)/3 

Thanks in advance!

EDIT: If you really think this is impossible to do, just say NO WAY! :) This is an acceptable answer, I really don't know if it can be done.

EDIT2: I am using SQL Server 2008. I would like to have different groups, but a fix. For example, the range is 10 seconds, 1 minute, 5 minutes, 30 minutes, 1 hour and 1 day (just an example, but something like that)

Answer 1

In SQL Server, you can use DATEPART, and then group hourly, minute, and second integer divisions.

 CREATE TABLE #times ( thetime time, A int ) INSERT #times VALUES ('10:00:01', 2) INSERT #times VALUES ('10:00:07', 4) INSERT #times VALUES ('10:00:10', 2) INSERT #times VALUES ('10:00:17', 1) INSERT #times VALUES ('10:00:18', 3) SELECT avg(A) -- <-- here you might deal with precision issues if you need non-integer results. eg: (avg(a * 1.0) FROM #times GROUP BY datepart(hour, thetime), DATEPART(minute, thetime), DATEPART(SECOND, thetime) / 10 DROP TABLE #times 

Answer 2

Someone may come and give you an answer with the full code, but the way I approach it is to break it down into several smaller tasks / solutions:

(1) Create a temporary table at intervals. See the accepted answer to this question:

Get a list of dates between two dates

This answer was for MySQL, but you need to get started. Googling "SQL Interval Creation" should also provide additional ways to do this. You want to use MAX (DateTime) and MIN (DateTime) from your main table as input to any method you use (and obviously 10 seconds for a range).

(2) Join the temp table with your main table with the join condition (pseudocode):

 FROM mainTable m INNER JOIN #tempTable t ON m BETWEEN t.StartDate AND t.EndDate 

(3) Now it should be as simple as SELECT and GROUPING correctly:

 SELECT AVG(mA) FROM mainTable m INNER JOIN #tempTable t ON m BETWEEN t.StartDate AND t.EndDate GROUP BY t.StartDate 

Edit: if you want to see intervals with those that have no records (zero average), you will have to twist the query, use LEFT JOIN and COALESCE on mA (see below). If you don't need such interlates, OCary's solution is better / cleaner.

 SELECT AVG(COALESCE(mA, 0)) FROM #tempTable t LEFT JOIN mainTable m ON m BETWEEN t.StartDate AND t.EndDate GROUP BY t.StartDate 

Answer 3

It depends on the DBMS used. In Oracle, you can do the following:

 SELECT AVG(A) FROM MYTABLE GROUP BY to_char(DateTime, 'HH24:MI') 

Answer 4

 CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dtime` datetime NOT NULL, `val` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; INSERT INTO `test` (`id`, `dtime`, `val`) VALUES (1, '2011-09-27 18:36:19', 8), (2, '2011-09-27 18:36:21', 4), (3, '2011-09-27 18:36:27', 5), (4, '2011-09-27 18:36:35', 3), (5, '2011-09-27 18:36:37', 2); 

---

 SELECT *, AVG(val) FROM test GROUP BY FLOOR(UNIX_TIMESTAMP(dtime) / 10) 

Answer 5

I accessed this using the Common Table expression to get all the periods between any dates of my data. Basically, you can change the interval to any SQL interval.

 DECLARE @interval_minutes INT = 5, @start_date DATETIME = '20130201', @end_date DATETIME = GETDATE() ;WITH cte_period AS ( SELECT CAST(@start_date AS DATETIME) AS [date] UNION ALL SELECT DATEADD(MINUTE, @interval_minutes, cte_period.[date]) AS [date] FROM cte_period WHERE DATEADD(MINUTE, @interval_minutes, cte_period.[date]) < @end_date ) , cte_intervals AS (SELECT [first].[date] AS [Start], [second].[date] AS [End] FROM cte_period [first] LEFT OUTER JOIN cte_period [second] ON DATEADD(MINUTE, 5, [first].[date]) = [second].[date] ) SELECT i.[Start], AVG(data) FROM cte_intervals i LEFT OUTER JOIN your_data mu ON mu.your_date_time >= i.Start and mu.your_date_time < i.[End] GROUP BY i.[Start] OPTION (MAXRECURSION 0) 

Answer 6

From http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=142634 you can also use the following query:

 select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ( value ) from yourtable group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0) 

which then expands to offer:

 Select a.MyDate, Start_of_10_Min = dateadd(mi,(datepart(mi,a.MyDate)/10)*10,dateadd(hh,datediff(hh,0,a.Mydate),0)) from ( -- Test Data select MyDate = getdate() ) a 

although I'm not too much as they plan to get the average in the second sentence.

Personally, I prefer OCary's answer , because I know what is going on there, and that I can figure it out in 6 months without looking again, but I turn it on for completeness.