Calculate Timestamp Concurrent Intervals

[c0dem0nkey]

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;
/

[gandolf989]

You might want to look at either the lead or lag analytic functions.

http://download-east.oracle.com/docs...s56a.htm#83619

[c0dem0nkey]

Thanks Gandolf!

So now I created a sql statement like this..

select pollsiteid,checkintime,LAG(checkintime,1) OVER (order by checkintime) as t2,timestamp_diff_in_seconds(checkintime,LAG(checkintime,1) OVER (order by checkintime)) as secdif from LOG_FIELD_CHECKIN_HISTORY where pollsiteid = 510

Which gives me the difference between the two records.

Now..I'm not to sure how to indicate the concurrent intervals..especially between two dates.

[gandolf989]

Thanks Gandolf!

So now I created a sql statement like this..

select pollsiteid,checkintime,LAG(checkintime,1) OVER (order by checkintime) as t2,timestamp_diff_in_seconds(checkintime,LAG(checkintime,1) OVER (order by checkintime)) as secdif from LOG_FIELD_CHECKIN_HISTORY where pollsiteid = 510

Which gives me the difference between the two records.

Now..I'm not to sure how to indicate the concurrent intervals..especially between two dates.

Did you know you can subtract one date from another and find out the difference in time.

[c0dem0nkey]

Ha...thanks.

I guess I'm note sure how to do it in a sql statement. Not so much the subtraction but concurrency.

[gandolf989]

Ha...thanks.

I guess I'm note sure how to do it in a sql statement. Not so much the subtraction but concurrency.

Both of these value represent the same thing. The first shows the hours, minutes and seconds of the current time as a decimal component of 1 day. i.e. since midnight .653946759 days have passed. The second shows the same thing in hours minutes and seconds.

So when you subtract one date from another you end up with a number, the whole portion of that number represents days, the decimal portion represents part of a day.

SQL> select sysdate-TRUNC(sysdate), to_char(sysdate, 'HH24:MI:SS:SSSS') from dual;
SYSDATE-TRUNC(SYSDATE) TO_CHAR(SYSDA
---------------------- -------------
            .653946759 15:41:41:4141

Does that help?