You may try something like this to get the Timestamps truncated to 15 Minutes:
Trunc_Timestamp(YourTimestamp, 'HH24') + (Minute(YourTimestamp) / 15 * 15) Minutes
Alternatively:
Trunc_Timestamp(YourTimestamp, 'HH24') + Case When Minute(YourTimestamp) between 0 and 14 Then 0
When Minute(YourTimestamp) between 15 and 29 Then 15
When Minute(YourTimestamp) between 30 and 44 Then 30
Else 45
End Minutes
... then you can easily group and count
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Friday, 6 September 2024 20:25
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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.
--
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.
midrange.com
