I have a table like this:

and the output should be difference between two consecutive rows.

so, the required result is:

the difference represents difference between two consecutive messages "message1" and "message2". There will be multiple p_ids and P_ID is a varchar() type, Message is also varchar() type and TIMESTAMP is TIMESTAMP type

CodePudding user response：

You can use the LEAD analytic function and then EXTRACT and TO_CHAR to format the difference:

SELECT timestamp,
       p_id,
       message,
       TO_CHAR(
         EXTRACT(DAY FROM difference) * 24
           EXTRACT(HOUR FROM difference),
         'FM999900'
       )
       || ':'
       || TO_CHAR(EXTRACT(MINUTE FROM difference), 'FM00')
       || ':'
       || TO_CHAR(EXTRACT(SECOND FROM difference), 'FM00') AS difference
FROM   (
  SELECT t.*,
         LEAD(timestamp, 1, timestamp) OVER (PARTITION BY p_id ORDER BY timestamp)
           - timestamp AS difference
  FROM   table_name t
)

Which, for the sample data:

CREATE TABLE table_name (TIMESTAMP, P_ID, Message) AS
SELECT TIMESTAMP '2019-01-11 09:54:48.980000  01:00', 'bim_123', 'message1' FROM DUAL UNION ALL
SELECT TIMESTAMP '2019-01-11 09:54:58.980000  01:00', 'bim_123', 'message2' FROM DUAL UNION ALL
SELECT TIMESTAMP '2019-01-11 09:56:08.980000  01:00', 'bim_123', 'message3' FROM DUAL UNION ALL
SELECT TIMESTAMP '2019-01-15 09:56:08.980000  01:00', 'bim_123', 'message4' FROM DUAL UNION ALL
SELECT TIMESTAMP '2019-01-16 09:56:08.980000  01:00', 'bim_123', 'message5' FROM DUAL UNION ALL
SELECT TIMESTAMP '2019-01-16 10:00:09.980000  01:00', 'bim_123', 'message6' FROM DUAL UNION ALL
SELECT TIMESTAMP '2019-01-16 11:30:09.980000  01:00', 'bim_123', 'message7' FROM DUAL;

Outputs:

TIMESTAMP	P_ID	MESSAGE	DIFFERENCE
2019-01-11 09:54:48.980000000 01:00	bim_123	message1	00:00:10
2019-01-11 09:54:58.980000000 01:00	bim_123	message2	00:01:10
2019-01-11 09:56:08.980000000 01:00	bim_123	message3	96:00:00
2019-01-15 09:56:08.980000000 01:00	bim_123	message4	24:00:00
2019-01-16 09:56:08.980000000 01:00	bim_123	message5	00:04:01
2019-01-16 10:00:09.980000000 01:00	bim_123	message6	01:30:00
2019-01-16 11:30:09.980000000 01:00	bim_123	message7	00:00:00

db<>fiddle here