I have a table that looks like this

CREATE TABLE LOG_FIELD_CHECKIN_HISTORY (
MYID NUMBER(9,0) PRIMARY KEY,
POLLSITEID NUMBER,
PWID NUMBER,
COMPMAC VARCHAR2(25),
COMPIP VARCHAR2(25),
SOAPMSG CLOB,
CHECKINTIME TIMESTAMP
);


Field systems will "check in" at set intervals which creates a new record in the table

I want to create the sql that will show me current intervals within a tolerance(set my the user...ie 20sec..or 60sec) per pollsiteid,per date.

For instance pollsiteid 100 was online between 3/14 8:00am - 5:00pm, 3/15 8:00am - 8:30am Down 8:31am - 5:00pm

I have been using this function to diff to timestamps...I'm just not sure how to bundle everything up.


CREATE OR REPLACE Function timestamp_diff_in_seconds (ts1 in timestamp, ts2 in timestamp)
return number is total_secs number;
diff interval day(9) to second(6);
begin
diff := ts2 - ts1;
total_secs := abs(extract(second from diff) + extract(minute from diff)*60 + extract(hour from diff)*60*60 + extract(day from diff)*24*60*60);

return total_secs;
end timestamp_diff_in_seconds;
/