Oracle dates: group rows into periods Oracle Tips by Burleson Consulting

 

Question: I have a table containing large data, and one of the fields is a date field. Every minute there are hundreds of rows in the table. I want to query only data that was inserted last every, lets say, 10 minutes, meaning: query one row per period of 10 minutes.

How do I group by a period of time, so that I can get the max(date) for each 10 minutes?

 

Answer:  In Oracle, you can get 10 minute chunks like this:

SELECT to_char(sysdate + (10/24/60), ‘HH:MI AM’) FROM dual;

to_char(sysdate + numtodsinterval(10, ‘MINUTE’), ‘HH:MI AM’)FROM dual;
 

SQL guru Laurent Schneider notes a great solution: 

 

"To trunc to 10 minutes, you must divide the day in 144 periods. To do the math, I calculate the difference with a date :

 

ID    D
1    2007-06-24 00:00:01
2    2007-06-24 00:00:02
3    2007-06-24 00:20:01
4    2007-06-24 00:22:01
5    2007-06-24 00:30:00
6    2007-06-24 00:35:00
7    2007-06-24 00:40:00

select ceil((d-date '-4712-01-01')*144)/144+date '-4712-01-01',
  max(d),
  max(id) keep (dense_rank last order by d,rowid)
from t
group by
  ceil((d-date '-4712-01-01')*144)/144+date '-4712-01-01';

CEIL((D-DATE'-4712-    MAX(D)               MAX(ID)KEEP(DENSE_RANKLASTORDERBYD,ROWID)
2007-06-24 00:10:00    2007-06-24 00:00:02    2
2007-06-24 00:30:00    2007-06-24 00:30:00    5
2007-06-24 00:40:00    2007-06-24 00:40:00    7"