CODEWITHSUNDEEP
sqloracle-database
Nir
Nir

Reputation: 2629

select difference between two rows

I have select the return to me:

NAME                            REBUILD
------------------------------  ---------
IDXD_INVOICE_LINE               25-01-14 17:00:58
IDXD_INST                       25-01-14 17:08:08
IDXD_IMPORT                     25-01-14 17:08:39
IDXD_IMPORT_FILE                25-01-14 18:08:02
IDXD_HP                         25-01-14 18:08:37
IDXD_TASK                       25-01-14 18:09:08
IDXD_RULE                       25-01-14 18:09:46
IDXD_USER                       26-01-14 03:48:57

I now want to get the difference between a row and the row above it, the first row will difference with 25-01-14 03:00:00. Result will be:

NAME                            REBUILD
------------------------------  ---------
IDXD_INVOICE_LINE               02:00:58
IDXD_INST                       00:08:08
IDXD_IMPORT                     00:00:31
IDXD_IMPORT_FILE                00:59:23
IDXD_HP                         00:00:35
IDXD_TASK                       00:00:31
IDXD_RULE                       00:00:38
IDXD_USER                       09:40:11

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

I assume the "row above" is based on the timestamp.

select name,
       to_char(trunc(sysdate) + (rebuild - prev_rebuild) / (24*60*60)), 'HH24:MI:SS')
from (select t.*, 
             lag(rebuild) over (order by rebuild) as prev_rebuild
      from t
     ) t;

This uses a trick to get the time output in the format you want it. It works when the difference is less than 24 hours.

EDIT:

If it can be longer than 24 hours, then you need to put the expression together yourself:

select name,
       (to_char(trunc((rebuild - prev_rebuild) * 24), '00') || ':' ||
        to_char(mod(trunc((rebuild - prev_rebuild)*24*60), 60), '00') || ':' ||
        to_char(mod(trunc((rebuild - prev_rebuild)*24*60*60), 60), '00')
       ) as timestamp
from (select t.*, 
             lag(rebuild) over (order by rebuild) as prev_rebuild
      from t
     ) t;

Upvotes: 1

Related Questions