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

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.