Home » SQL & PL/SQL » SQL & PL/SQL » Time difference between 2 rows (oracle 12c)
Time difference between 2 rows [message #671682] Wed, 12 September 2018 15:23 Go to next message
azeem87
Messages: 99
Registered: September 2005
Location: dallas
Member
Hi, I am trying to get the time difference between 2 rows for the same Trans_id, that has 2 different trans_cd
when the transaction start TRANS_CD='SVP08' and when the transaction completes TRans_cd='SVP01'
basically I need the output as example TRANS_ID=100000053487855 and difference=2 secs.

sample create table, insert statement and select is paste, Please advice.
CREATE TABLE TRANS_LOG_INFO
(
  TRANS_CD         VARCHAR2(10 BYTE)              NOT NULL,
  DT_TIME        DATE                           NOT NULL,
  TRANS_ID        VARCHAR2(35 BYTE)) ;
  
 SET DEFINE OFF;
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487850', 'SVP01', TO_DATE('09/12/2018 10:30:36', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487850', 'SVP08', TO_DATE('09/12/2018 10:30:36', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487855', 'SVP08', TO_DATE('09/12/2018 10:31:40', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487855', 'SVP01', TO_DATE('09/12/2018 10:31:42', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487865', 'SVP01', TO_DATE('09/12/2018 10:33:06', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487865', 'SVP08', TO_DATE('09/12/2018 10:33:06', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487880', 'SVP01', TO_DATE('09/12/2018 10:38:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487880', 'SVP08', TO_DATE('09/12/2018 10:38:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487895', 'SVP08', TO_DATE('09/12/2018 10:38:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487895', 'SVP01', TO_DATE('09/12/2018 10:38:22', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487910', 'SVP01', TO_DATE('09/12/2018 10:38:28', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487910', 'SVP08', TO_DATE('09/12/2018 10:38:28', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487920', 'SVP08', TO_DATE('09/12/2018 10:38:31', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487920', 'SVP01', TO_DATE('09/12/2018 10:38:32', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487925', 'SVP01', TO_DATE('09/12/2018 10:38:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487925', 'SVP08', TO_DATE('09/12/2018 10:38:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487940', 'SVP01', TO_DATE('09/12/2018 10:38:38', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487940', 'SVP08', TO_DATE('09/12/2018 10:38:38', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487950', 'SVP08', TO_DATE('09/12/2018 10:38:51', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487950', 'SVP01', TO_DATE('09/12/2018 10:38:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487955', 'SVP01', TO_DATE('09/12/2018 10:38:59', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
   (TRANS_ID, TRANS_CD, DT_TIME)
 Values
   ('100000053487955', 'SVP08', TO_DATE('09/12/2018 10:38:59', 'MM/DD/YYYY HH24:MI:SS'));


  commit;
  
  SELECT  TRANS_ID, TRANS_CD, DT_TIME FROM TRANS_LOG_INFO WHERE TRANS_CD in ('SVP08','SVP01') AND DT_TIME >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS') 

100000053487850	SVP01	9/12/2018 10:30:36 AM
100000053487850	SVP08	9/12/2018 10:30:36 AM
100000053487855	SVP08	9/12/2018 10:31:40 AM
100000053487855	SVP01	9/12/2018 10:31:42 AM
100000053487865	SVP01	9/12/2018 10:33:06 AM
100000053487865	SVP08	9/12/2018 10:33:06 AM
100000053487880	SVP01	9/12/2018 10:38:03 AM
100000053487880	SVP08	9/12/2018 10:38:03 AM
  

For some rows they may not be any difference in time between trans start and end then just the trans_id and difference =0.

Thanks
Re: Time difference between 2 rows [message #671683 is a reply to message #671682] Wed, 12 September 2018 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 26121
Registered: January 2009
Location: SoCal
Senior Member
DATE1 MINUS DATE2 = XXX WHERE XXX IS IN UNITS OF DAYS
XXX*24 = HHHH HOURS
HHHH*60 = MIN minutes
MIN*60 = SSSS seconds
Re: Time difference between 2 rows [message #671684 is a reply to message #671683] Wed, 12 September 2018 16:14 Go to previous messageGo to next message
azeem87
Messages: 99
Registered: September 2005
Location: dallas
Member
Thanks for looking into it,
there is only 1 Date column,
sorry not sure what you mean by date1 minus date2.
basically I am trying to get the difference in time between 2 rows for the same Trans_id for trans_cd SVP01-SVP08,
Re: Time difference between 2 rows [message #671685 is a reply to message #671684] Wed, 12 September 2018 16:47 Go to previous messageGo to next message
pablolee
Messages: 2864
Registered: May 2007
Location: Scotland
Senior Member
use common table expression to create 2 datasets, 1 for svp01 and 1 for spv08
Join the two ctes on the trans id
subtract one dt_time from the other and convert the difference to seconds.

with t1 as (SELECT  TRANS_ID, TRANS_CD, DT_TIME 
            FROM TRANS_LOG_INFO 
            WHERE TRANS_CD = 'SVP08'
            AND DT_TIME >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS'))
, t2 as (SELECT  TRANS_ID, TRANS_CD, DT_TIME 
            FROM TRANS_LOG_INFO 
            WHERE TRANS_CD = 'SVP01'
            AND DT_TIME >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS'))            
select * 
from t1
join t2
on trans_id

[Updated on: Wed, 12 September 2018 16:58]

Report message to a moderator

Re: Time difference between 2 rows [message #671688 is a reply to message #671682] Thu, 13 September 2018 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
For some rows they may not be any difference in time between trans start and end then just the trans_id and difference =0.
Post the result you want for the data you gave.

Re: Time difference between 2 rows [message #671689 is a reply to message #671682] Thu, 13 September 2018 01:54 Go to previous messageGo to next message
John Watson
Messages: 7545
Registered: January 2010
Location: Global Village
Senior Member
Another solution -
pdby1>
pdby1> select trans_id,(max(dt_time)-min(dt_time))*86400 from trans_log_info group by trans_id order by trans_id;

TRANS_ID                            (MAX(DT_TIME)-MIN(DT_TIME))*86400
----------------------------------- ---------------------------------
100000053487850                                                     0
100000053487855                                                     2
100000053487865                                                     0
100000053487880                                                     0
100000053487895                                                     1
100000053487910                                                     0
100000053487920                                                     1
100000053487925                                                     0
100000053487940                                                     0
100000053487950                                                     1
100000053487955                                                     0

11 rows selected.

pdby1>
How does it compare to Pablo's?
Re: Time difference between 2 rows [message #671690 is a reply to message #671685] Thu, 13 September 2018 01:55 Go to previous messageGo to next message
John Watson
Messages: 7545
Registered: January 2010
Location: Global Village
Senior Member
I spotted the deliberate mistake! USING, not ON.
Re: Time difference between 2 rows [message #671691 is a reply to message #671685] Thu, 13 September 2018 04:11 Go to previous messageGo to next message
cookiemonster
Messages: 13230
Registered: September 2008
Location: Rainy Manchester
Senior Member
CTE seems unnecessary for something this simple:

SELECT tlie.trans_id, (tlie.dt_time - tlis.dt_time) * (24 * 60 * 60) AS time_diff
FROM trans_log_info tlie
JOIN trans_log_info tlis ON tlie.trans_id = tlis.trans_id
WHERE tlis.trans_cd = 'SVP08'
AND tlie.trans_cd = 'SVP01'
AND tlis.dt_time >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS')
ORDER BY 1;
icon14.gif  Re: Time difference between 2 rows [message #671750 is a reply to message #671691] Mon, 17 September 2018 15:50 Go to previous messageGo to next message
azeem87
Messages: 99
Registered: September 2005
Location: dallas
Member
Thanks all for your inputs,
I took the last one from cookiemonster, updated with other required columns and joins with other tables, it worked perfectly.

Thanks Again.
Re: Time difference between 2 rows [message #671757 is a reply to message #671750] Tue, 18 September 2018 08:48 Go to previous messageGo to next message
Bill B
Messages: 1782
Registered: December 2004
Senior Member
The following query uses the Lag Function
SELECT Trans_id,
       Trans_cd,
       Dt_time,
       (Dt_time - (LAG(Dt_time, 1)
           OVER    (PARTITION BY Trans_id
       ORDER BY     Trans_id, CASE WHEN Trans_cd = 'SVP08' THEN 1 ELSE 2 END
       ))) * (60 * 60 * 24)
           Seconds
FROM Trans_log_info
WHERE Trans_cd IN ('SVP08', 'SVP01')
  AND Dt_time >= TO_DATE('09/12/2018 10:06:00', 'MM/DD/YYYY HH24:MI:SS')
ORDER BY Trans_id, CASE WHEN Trans_cd = 'SVP08' THEN 1 ELSE 2 END;
Re: Time difference between 2 rows [message #671758 is a reply to message #671757] Tue, 18 September 2018 09:10 Go to previous message
JPBoileau
Messages: 36
Registered: September 2017
Member
That's the better solution, with Windowing functions. Less data reads.

JP
Previous Topic: Adding rownum in pagination query in oralce 12c
Next Topic: Help w/ REGEXP_SUBSTR
Goto Forum:
  


Current Time: Tue Sep 18 16:27:51 CDT 2018