Re: Grouping by time intervals

select trunc(created) + ((trunc(((created -trunc(created)) * 1440)/15)) * 15)/1440, count(*)
from dba_users
group by trunc(created) + ((trunc(((created -trunc(created)) * 1440)/15)) * 15)/1440;

Above example uses 15 minutes as the interval.

TRUNC(CREATED)+((TR COUNT(*)

------------------- ----------
12/21/2001 15:30:00          3
01/02/2002 10:45:00         30
01/11/2002 12:00:00          1
01/15/2002 13:00:00          2
01/28/2002 10:00:00          1
02/04/2002 15:45:00          1
02/12/2002 09:45:00          1
02/13/2002 15:45:00          1
02/14/2002 09:00:00          1
02/18/2002 16:30:00          2
03/05/2002 17:15:00          2
03/05/2002 17:45:00          1
03/06/2002 15:30:00          4
03/06/2002 18:30:00          3

Here it is with a 2 hour interval, same source data set [Notice 3/05/2002]:

SQL> select trunc(created) + ((trunc(((created -trunc(created)) * 1440)/120)) * 120)/1440, count(*) from dba_users   2 group by trunc(created) + ((trunc(((created -trunc(created)) * 1440)/120)) * 120)/1440;

TRUNC(CREATED)+((TR COUNT(*)

------------------- ----------
12/21/2001 14:00:00          3
01/02/2002 10:00:00         30
01/11/2002 12:00:00          1
01/15/2002 12:00:00          2
01/28/2002 10:00:00          1
02/04/2002 14:00:00          1
02/12/2002 08:00:00          1
02/13/2002 14:00:00          1
02/14/2002 08:00:00          1
02/18/2002 16:00:00          2
03/05/2002 16:00:00          3
03/06/2002 14:00:00          4
03/06/2002 18:00:00          3

13 rows selected.

Kenneth C Stahl wrote:

> I have a table with a bunch of rows that all have date fields. I would
> like to be able to do an extract where I can summarize some values by
> pre-defined time periods. The time periods that I am particular looking
> for are 10,15,30 and 60 but the time interval could also be multiples of
>
> hours like ever 2 hours, every 8 hours, etc.. Thus, if I summarized by
> 15 minutes, my times would look like:
>
> 03/21/2002 09:00 <count>
> 03/21/2002 09:15 <count>
> 03/21/2002 09:30 <count>
> 03/21/2002 09:45 <count>
> .
> .
> .
> etc.
>
> and if I sent for two hour intervals it would look like
>
> 03/21/2002 0900 <count>
> 03/21/2002 1100 <count>
> 03/21/2002 1300 <count>
> 03/21/2002 1500 <count>
> .
> .
> .
> etc.
>
> Is there any good, elegant way of doing this that is easy to modify
> whenever I want to change the time interval and can be expressed as a
> single algorithm regardless of whether I want intervals within hours or
> multiples of hours?
>
> Ultimately the query will be in a stored procedure and I'll pass the
> time interval in as a parameter expressed in minutes. So, the value
> could be anything from 1 to 1440
>
> TIA
Received on Thu Mar 28 2002 - 16:17:05 CST