difference between timestamp in oracle in the same column

Question

I have the below records in call_event table;

EVENT_ID   TIMESTAMP             EVENT   
---------  -------------------   ------
9000       03/27/2017 18.00.39   Call Start
9000       03/27/2017 18.08.44   Call END
9001       03/28/2017 14.30.27   Call Start
9001       03/28/2017 15.02.56   Call END

What query should I write in oracle to get the below result

EVENT_ID   TIMESTAMP             EVENT      Call Duration
---------  -------------------   ------     -------------   
9000       03/27/2017 18:00:39   Call Start  00:09:23
9000       03/27/2017 18:08:44   Call END    00:09:23
9001       03/28/2017 14.30.27   Call Start  00:33:29
9001       03/28/2017 15.02.56   Call END    00:33:29

Your call durations seem not to match the timestamp differences. — collapsar, Mar 24 '17 at 11:10

score 0 · Answer 1 · answered Mar 24 '17 at 10:59

Why would you want two rows for a single call? I would be inclined to pivot the data:

select event_id, min(timestamp) as call_start_ts, max(timestamp) as call_end_ts,
       max(timestamp) - min(timestamp) as duration
from t
group by event_id;

If the timestamp column is indeed a timestamp, then duration will be an interval. The above assumes that calls start before they end.

Having said that, you can do the same thing with window functions if you want separate rows::

select event_id, min(timestamp) as call_start_ts, max(timestamp) as call_end_ts,
       (max(timestamp) over (partition by event_id) -
        min(timestamp) over (partition by event_id)
       ) as duration
from t
group by event_id;

I think some phone logging software does that sort of thing. They probably find it easier to add a record that to update one. — Manngo, Mar 24 '17 at 23:12
@Manngo . . . I meant in the result set, not in the source data. It makes lots of sense in the source data. — Gordon Linoff, Mar 24 '17 at 23:18

score 0 · Answer 2 · answered Mar 24 '17 at 11:03

Can this help you

SELECT A.EVENT_ID, A.TIMESTAMP, A.EVENT, B.TIMESTAMP AS TIMESTAMP_END, B.EVENT AS EVENT_END, B.TIMESTAMP - A.TIMESTAMP AS DURATION
FROM CALL_EVENT A 
INNER JOIN  CALL_EVENT B ON A.EVENT_ID = B.EVENT_ID 
WHERE A.EVENT = 'Call Start'
AND B.EVENT = 'Call END'

score 0 · Answer 3 · edited May 23 '17 at 11:54

The trickiest part is the formatting of the datetime intervals involves as there is no shrink-wrapped function in oracle akin to the TO_CHAR(...) family:

SELECT event_id
     ,           to_char(extract(HOUR   FROM delta), 'fm00')
       || ':' || to_char(extract(MINUTE FROM delta), 'fm00')
       || ':' || to_char(extract(SECOND FROM delta), 'fm00') duration
  FROM (
            SELECT event_id
                 , max(ts) - min(ts) delta
              FROM events
          GROUP BY event_id
       )
     ;

A variant handles timestamps that are modelled as DATE columns:

SELECT event_id
     ,           to_char(extract(HOUR   FROM delta), 'fm00')
       || ':' || to_char(extract(MINUTE FROM delta), 'fm00')
       || ':' || to_char(extract(SECOND FROM delta), 'fm00') duration
  FROM (
            SELECT event_id
                 , numtodsinterval ( max(ts) - min(ts), 'DAY' ) delta
              FROM events
          GROUP BY event_id
       )
     ;

Tested on oracle 12c1.

Credits: Interval formatting taken from this SO answer

score 0 · Accepted Answer · answered Mar 24 '17 at 12:05

Oracle Setup:

CREATE TABLE call_event ( EVENT_ID, "TIMESTAMP", EVENT ) AS
SELECT 9000, TIMESTAMP '2017-03-27 18:00:39', 'Call Start' FROM DUAL UNION ALL
SELECT 9000, TIMESTAMP '2017-03/27 18:08:44', 'Call END' FROM DUAL UNION ALL
SELECT 9001, TIMESTAMP '2017-03-28 14:30:27', 'Call Start' FROM DUAL UNION ALL
SELECT 9001, TIMESTAMP '2017-03-28 15:02:56', 'Call END' FROM DUAL;

Query:

SELECT t.*,
       MAX( "TIMESTAMP" ) OVER ( PARTITION BY EVENT_ID )
         - MIN( "TIMESTAMP" ) OVER ( PARTITION BY EVENT_ID )
         AS "Call Duration"
FROM   call_event;

Output:

EVENT_ID TIMESTAMP           EVENT      Call Duration
-------- ------------------- ---------- -------------------
    9000 2017-03-27 18:00:39 Call Start +00 00:08:05.000000
    9000 2017-03-27 18:08:44 Call END   +00 00:08:05.000000
    9001 2017-03-28 14:30:27 Call Start +00 00:32:29.000000
    9001 2017-03-28 15:02:56 Call Start +00 00:32:29.000000

difference between timestamp in oracle in the same column

4 Answers4