Time slicing in Oracle/SQL

Question

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

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