Oracle timestamp difference SQL

Often in performance testing we need to get processing times of transactions which have been timestamped in a log somewhere. This usually entails multiple log entries for the same transaction which have been processed for in and out entries. Consider timekeeper entries for example, employees clocking in for work and out of work multiple times thoughout the day. Read on for an example SQL that can be helpful

Consider the following log

TXN date_created Event
TXN A 10/DEC/2009 08:00::54 AM IN
TXN B 10/DEC/2009 08:10::54 AM IN
TXN C 10/DEC/2009 09:05::03 AM IN
TXN B 10/DEC/2009 12:10::54 PM OUT
TXN B 10/DEC/2009 12:20::54 PM IN
TXN A 10/DEC/2009 01:15::01 PM OUT
TXN C 10/DEC/2009 06:05::03 PM IN
TXN A 10/DEC/2009 01:30::06 PM IN
TXN A 10/DEC/2009 04:30::06 PM OUT
TXN B 10/DEC/2009 04:35::16 PM OUT
TXN C 10/DEC/2009 06:25::12 PM OUT

The following SQL will take the 1st timestamp for each of the unique transactions and the last timestamp and then subtract the difference, providing the overall turnaround time for each transaction. Now this only provides an initial high level view of overall processing time and doesnt provide a breakdown of each event. Anyhow its a good start. The SQL provdies a comma separated output than can then be exported into excel and graphed. The output is in seconds, so this is only good for 99999 seconds. Therefore if more than a day you will need to extract days as well. Anyhow here it is.

 

SELECT txn||','||Min(date_created)||','||
(TO_NUMBER(TO_CHAR(Max(date_created),'SSSSS'))-TO_NUMBER(TO_CHAR(Min(date_created),'SSSSS')))
from sometable where txn like '%ESB%' and date_created > '11/FEB/10 04:10:00.00 PM'
GROUP BY txn order by txn;

There is a PL/SQL enhancement of this if you want to automate it further

set echo off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ''
set trimspool on
spool nicko.txt
SELECT txn||','||Min(date_created)||','||
(TO_NUMBER(TO_CHAR(Max(date_created),'SSSSS'))-TO_NUMBER(TO_CHAR(Min(date_created),'SSSSS')))
from sometable where txn like '%ESB%' and date_created > '11/FEB/10 04:10:00.00 PM'
GROUP BY txn order by txn;
spool off

This is only a guide. Hope it helps