Re: Grouping Results In 5 Minute Increments

From: Lee Miller <lpm_at_newsguy.com>
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

Original text of this message