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

Question

I have a table like this one:

DateTime   A

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

Is this possible to create a query that returns me the average value of A each 10 seconds? In this case the result would be:

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

Thanks in advance!

EDIT: If you really think that this can not be done just say NO WAY! :) It's an acceptable answer, I really don't know if this can be done.

EDIT2: I'm using SQL Server 2008. I would like to have different groupings but fixed. For example, ranges each 10 sec, 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 by hour, minute and second integer-division 10.

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

It depends on DBMS you are using. In Oracle you can do the following:

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

Answer 3

I approached this by using a Common Table Expression to get all the periods between any given dates of my data. In principal you could 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 4

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

(1) Create a temp table with intervals. See the accepted answer on this question:

Get a list of dates between two dates

This answer was for MySQL, but should get you started. Googling "Create intervals SQL" should also yield additional ways to accomplish this. You will want to use the MAX(DateTime) and MIN(DateTime) from your main table as inputs into whatever method you use (and 10 seconds for the span, obviously).

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

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

(3) Now it should be as simple as correctly SELECTing and GROUPing:

SELECT 
  AVG(m.A)     
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 that have no records (zero average), you would have to rearrage the query, use a LEFT JOIN, and COALESCE on m.A (see below). If you don't care about seeing such interals, OCary's solution is better/cleaner.

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

Answer 5

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 6

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

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 someone then expands upon to suggest:

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 how they plan on getting the average in in the second suggestion.

Personally I prefer OCary's answer as I know what is going on there and that I'll be able to understand it in 6 months time without looking it up again but I include this one for completeness.