Re: sql to read timestamp in row in a product count for every 15 minute increment

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I can think of a couple of ways...

Something like so
with tmp as (
   select
     *date*(log_time) as theDate
    ,  *hour*(log_time) as theHour
    ,  *minute*(log_time) / *15* as theQtrHour
  from mylog
)
select theDate, theHour, theQtrHour, *count*(*) as cnt
from tmp
group by thedate, theHour, theQtrHour;

where "theQtrHour" is 0,1,2,3 You could multiply by 15 and recreate a
time/timestamp if you'd like

Other other would be a table you could join to
I don't recall off the top of my head if you can use BETWEEN in a join
condition.
If so, then you only need 96 rows...
Period,    startTime, endTime
00:00:00   00:00:00.000 00:14:59.999
00:15:00   00:15:00.000 00:29:59.999
<<snip>>

If not, then you'd 1440
Period,    minOfTheDat
00:00:00   00:01:00.000
00:00:00   00:02:00.000
<<snip>>
00:15:00   00:15:00.000
00:15:00   00:16:00.000

and you could join time(round_timestamp(log_time,'MI')) = minOfTheDay

Call it a "time table" (as opposed to a "Calendar/Dates Table"

Generating it wouldn't be too difficult and would only need to be done once
and could be reused when needed.

JOIN'ing against a Calendar table tends to be more performant than run-time
calculations.

I'd probably check both ways if this was more than just a one-time thing.

HTH,
Charles




On Fri, Sep 6, 2024 at 10:45 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

> Been struggling with this a bit ... not gonna lie.
>
> Seem many examples in other flavors of sql's but nothing in db2...
>
>
> given a table, with rows that simply contain a timestamp...
> I need a count of the rows within the 15 min increments of those
> timestamps.
>
> does anyone want to at least try and get me started?
> Won't lie, the entire query would be great too - I'm sure I'd tuck it away
> as something to understand and continue learning upon.
>
> tia
>
> Jay
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.