Querying a time range

[scottyjohn]

Hi all,
I have a view which has the following columns,

STARTTIME
ENDTIME

The data is in the format hh:mm:ss but I think it is actually in text format rather than time as such. The underlying table has this column in UTC, and a conversion is done in the structure of the view to give us the time as above. Each record is an event with a starttime and endtime. What I have been asked to produce is a way to tell for a given minute, how many concurrent event are in progress? I cant think how to achieve this. If it was the other way round and it was to see how many records' starttimes fell between two times, then I could use between, but as the entry must look at the starttime and endtime of each record and work out if in its duration it matched the criteria time, I cant figure out if this is possible? Pseudo code would look something like this I think....

select count(*)
from tableA
where times between starttime and Endtime
= '22:54:00'

I hope this makes sense and thanks in advance for any help


John

 

[SantaMufasa]

I think it is actually in text format rather than time as such.

John, guessing won't work in this case...to solve this issue, we need to know definitively what the data types are for "STARTTIME" and "ENDTIME".

Could you please post the results of a SQL*Plus "DESCRIBE TableA"?

Next, when you say "...where times between starttime and Endtime = '22:54:00'", are you trying to say, "Show the rows where '22:54:00' (i.e., 10:54 p.m.) falls between the starttime and the endtime"? Or are you saying that you want to show those rows where the elapsed time difference between starttime and endtime is 22 hours and 54 minutes?





Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

Well, it seems if a concurrent jobs' (CJ) start time is at or before the event's end time and the CJ's end time is at or after after the events start time, then the CJ occurred during the event.
So, assuming you are correct and the event's date IS a text representation of hh:mm:ss, how do you propose to tell what DAY it happened on? Once you figure that out, you can use TO_DATE to convert the times to a date.

For now, let's pretend they're really dates. I'll also assume you have a view called EVENTS and a table called CJ (although it might be something like FND_CONCURRENT_REQUESTS).
Then I would think something like

SELECT e.event_id, count(c.cj_id) 
FROM events e, cj c
WHERE c.start_date <= e.end_date
  AND c.end_date >= e.start_date
GROUP BY e.event_id;

I haven't tested this, but I think it ought to work.