ORACLE-Find the Time Difference Between Two Steps

Suppose that you have a table which has only one time column. This table is used for collect the datetime for each steps of your transactions. (Eg. step started, step in progress, step closed for each transaction) In this example below the Pkey column is the unique key for each transaction and the second column is the start time for it.

pkey	datecreated
10	06/02/2007 10:28:25
10	06/02/2007 11:28:25
10	06/02/2007 12:28:25
20	06/02/2007 10:28:25
20	06/02/2007 09:28:25
20	06/02/2007 10:20:25
30	06/02/2007 10:28:25
30	07/02/2007 10:28:25
30	07/02/2007 01:28:25

solution 1 :


SELECT u.pkey,u.orderno,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24)) hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)) minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60)) seconds
FROM
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) orderno FROM table e) u,
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) orderno FROM table e) g
WHERE u.pkey = g.pkey(+) AND u.orderno = g.orderno(+)+1


solution 2 :


WITH std AS
(SELECT e.*,row_number() over (PARTITION BY pkey ORDER BY e.datecreated) orderno FROM table e)
(SELECT
u.pkey,u.orderno,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24)) hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)) minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60)) seconds
FROM std u , std g
WHERE u.pkey = g.pkey(+) AND u.orderno = g.orderno(+)+1
)


solution 3 :


SELECT pkey, datecreated,
TRUNC(((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24)/7) weeks,
TRUNC((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24) days,
TRUNC(((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)-24*(TRUNC((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24)) hours,
TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)-60*(TRUNC(((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)) minutes,
TRUNC(86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))-60*(TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)) seconds
FROM table