Time slicing in Oracle/SQL

Question

Hello,

I have a large-ish Oracle table containing rows representing units of work, with columns for start time and end time in addition to other meta-data.

I need to generate usage graphs from this data, given some arbitrary filtering criteria and a reporting time period. E.g., show me a graph of all of Alice's jobs for the 24-hour period starting last Tuesday at 7:00am. Each DB row will stack vertically in the graph.

I could do this in a high-level language by querying all potentially relevant rows, time slicing each one into 1-minute buckets, and graphing the result. But is there an efficient way to do this time slicing in SQL? Or is there an existing Oracle technology that does this?

Thanks!

Answer 1

Your best bet is to have a table (a temporary one generated on the fly would be fine if the time-slice is dynamic) and then join against that.

Answer 2

In terms of getting the data out, you can use 'group by' and 'truncate' to slice the data into 1 minute intervals. eg:

SELECT user_name, truncate(event_time, 'YYYYMMDD HH24MI'), count(*)
FROM job_table
WHERE event_time > TO_DATE( some start date time)
AND user_name IN ( list of users to query )
GROUP BY user_name, truncate(event_time, 'YYYYMMDD HH24MI')

This will give you results like below (assuming there are 20 rows for alice between 8.00 and 8.01 and 40 rows between 8.01 and 8.02):

Alice  2008-12-16 08:00   20
Alice  2008-12-16 08:01   40

Answer 3

This should do it fairly well. It will breakdown to the minute (1/1440 of a day).

SELECT 
  to_char(Times.time,'hh24:mi'),
  count(*)   
FROM   
  (SELECT
     time
   FROM  
     dual
   WHERE 
     1=2 
   MODEL 
     dimension by ( 0 as key )
     measures     ( sysdate -1 as time )
     rules upsert ( time[ for key from 0 to 1 increment (1/1440) ] = sysdate-1 + cv(key)) ) Times,
  job_table
WHERE 
  begintime <= Times.time 
AND 
  endtime > Times.time 
AND
  user_name = 'Alice'
GROUP BY 
  Times.time 
ORDER BY
  Times.time

I'm sure there is a faster way to do this, but this is the best I can come up with. Hope this helps.