Group by 10 minutes
584873Mar 9 2010 — edited Nov 27 2012Hi all,
Oracle 11 g
I have the following table and data
CREATE TABLE dummy_data (
ID number NOT NULL
,test_Timestamp timestamp NOT NULL
);
insert into dummy_data (id,test_TimeStamp) values (1,'08-MAR-10 09.43.30.922000000');
insert into dummy_data (id,test_TimeStamp) values (2,'08-MAR-10 09.46.30.922000000');
insert into dummy_data (id,test_TimeStamp) values (3,'08-MAR-10 09.23.30.922000000');
insert into dummy_data (id,test_TimeStamp) values (4,'08-MAR-10 09.26.30.922000000');
insert into dummy_data (id,test_TimeStamp) values (5,'08-MAR-10 09.13.30.922000000');
insert into dummy_data (id,test_TimeStamp) values (6,'08-MAR-10 09.12.30.922000000');
I need to get the average of id values , grouped by 10 minute interval of test_timestamp values. How can I do that ?
For hourly grouping I did the following and it works, but not sure how to do it for 10 minute interval
select to_char(test_Timestamp, 'yyyy-mm-dd hh24') as ts,
avg(id) as id
from dummy_data
GROUP BY to_char(test_Timestamp, 'yyyy-mm-dd hh24') ;
Thanks
_Pete