Re: Grouping Results In 5 Minute Increments
Date: Tue, 15 May 2001 08:35:57 -0400
Message-ID: <9dr7po0s1t_at_enews1.newsguy.com>
Hmm.. this should work, a little ugly put it's straight SQL no PL/SQL necessary. I only tested with a small table (10 values) so not sure of performance on a large table, also no testing done for null dates.
col1 is column containing dates
col2 is addition column of values to sum/avg/whatever
tab1 is table containing the columns
select
to_char( col1, 'DD-MON-YYYY HH24:')||
substr( to_char( col1, 'MI' ),1,1)|| (5 * trunc( .2 * to_number( substr( to_char( col1, 'MI' ),2,1 )))),sum( col2 )
from
tab1
group by
to_char( col1, 'DD-MON-YYYY HH24:')||
substr( to_char( col1, 'MI' ),1,1)|| (5 * trunc( .2 * to_number( substr( to_char( col1, 'MI' ),2,1 ))));
The other choice is to use an epoch date and subtract your actual values from it, multiply it up to the point you can round/trunc it. But this was easier than figuring out the conversion factor in fractional days.
"Ted" <ted_at_nowhere.net> wrote in message
news:WhVL6.952$6j3.88814_at_www.newsranger.com...
> I need to gather data in 5 minute clusters to perform a 5 minute average
report.
> I've been trying to forulate a query using TRUNC and GROUP BY, but the
problem
> I'm running into is that I can't figure out how to make TRUNC operate on
> intervals between minutes and hours. All I see in the documentation is
> TRUNC(somedate, 'HH') and TRUNC(somedate, 'MI'). I've tried dividing the
trunc'd
> dates, but I get incompatible data type errors.
>
> I'm pretty new to PL/SQL and I'm sure this type of thing has been done
before,
> but I'm pretty much at my wits end.
>
> Any help/advice would be appreciated.
>
> TIA!
>
> -----
> Ted
>
>
Received on Tue May 15 2001 - 14:35:57 CEST