Counting minutes in a date/time range [message #660044] |
Tue, 07 February 2017 17:41 |
|
dale2k9
Messages: 5 Registered: February 2017
|
Junior Member |
|
|
I have a table of process runs where the structure is basically jobname, startdatetime, enddatetime. It spans the past year and has a few million rows. The time span for a jobrun could be from a few seconds to a few hours. In order to get a view of system utilization for planning purposes, I'd like to get a total of jobs that are running on a per-minute basis. I can get the minute by epoch time/60 but I need a sum of each minute for which there is one or more jobs running across all of the entries.
My first thought, less thinking required but probably more work and processing, is to write a function that looks at each row, loops through the minutes from start time to stop time, and adds a row to a table for each minute, including the job name, and I can group with count on the table later. The advantage to this is that I can drill in to get a few of each jobname as well.
Alternatively, I can add a row for each minute found if there isn't a row and increment the row for each minute across all of the job runs. This is a much smaller result set but doesn't give the drill down opportunity.
What other options are there? How could I select the existing data to get a sum of utilized minutes without making a copy?
|
|
|
|
|
|
|
Re: Counting minutes in a date/time range [message #660087 is a reply to message #660044] |
Wed, 08 February 2017 11:41 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Not having your table definition, I can't test but is this logic something that you could work from:WITH minutes
AS (SELECT To_date('2016-01-01', 'yyyy-mm-dd') + ROWNUM / 1440 AS minute
FROM dual
CONNECT BY LEVEL < 365 * 1440)
SELECT minute,
Count(*)
FROM minutes
join process_run_table
ON( minutes.minute BETWEEN process_run_table.startdatetime AND
process_run_table.enddatetime )
GROUP BY minute;
|
|
|
|
|
|
|
|
|
|