How to extract timestamp differences in hours and do cumulative sum-CodePudding

CREATE TABLE test (
    id            NUMBER(10),
    due_dt        TIMESTAMP(6),
    status        VARCHAR2(10),
    created_on    TIMESTAMP(6),
    act_taken_on  TIMESTAMP(6)
);

insert into test values(1,'21-SEP-22 02.53.10.016537 AM','created','19-SEP-22 02.53.10.016537 AM','20-SEP-22 02.53.10.016537 AM');
insert into test values(1,'21-SEP-22 02.53.10.016537 AM','created','20-SEP-22 02.53.10.016537 AM','21-SEP-22 02.53.10.016537 AM');
insert into test values(2,'21-SEP-22 02.53.10.016537 AM','Approved','21-SEP-22 02.53.10.016537 AM','22-SEP-22 02.53.10.016537 AM');

DB Version: Oracle SQL Developer 18c

I have one table from which I need to calculate the difference between the timestamp and need to show only the hours differences. As per the below explanation:

Need to populate below columns:

aging : When status is created then take differences between created_on and act_taken_on and it should show only the hours differences. If the id is repeating then it should sum up with the previous value. In my sample data set for the id 1 there are two rows so the for the first row aging column will be 24 hrs approx. and for the next id 1 it should sum up with the previous value which is 24 hrs and give the result as 48 hrs approx.

time_left: difference between due_dt and sysdate and should show differences only in hours.

My attempt:

SELECT id,
CASE
when status = 'created' then (created_on - act_taken_on)
when status = 'Approved' then (created_on - act_taken_on)
end aging,
(due_dt - sysdate) time_left
from test;

Expected output:

 ---- --------- ----------- 
| id |  Aging  | time_left |
 ---- --------- ----------- 
|  1 | 24Hours | 48hours   |
|  1 | 48Hours | 48Hours   |
|  2 | 24Hours | 48Hours   |
 ---- --------- -----------

CodePudding user response：

Here's one option, which casts timestamps as date as you only need rounded hours and uses sum function in its analytic form.

Sample data:

SQL> select * from test order by id, created_on;

 ID DUE_DT                    STATUS     CREATED_ON                ACT_TAKEN_ON
--- ------------------------- ---------- ------------------------- -------------------------
  1 21.09.22 02:53:10,016537  created    19.09.22 02:53:10,016537  20.09.22 02:53:10,016537
  1 21.09.22 02:53:10,016537  created    20.09.22 02:53:10,016537  21.09.22 02:53:10,016537
  2 21.09.22 02:53:10,016537  Approved   21.09.22 02:53:10,016537  22.09.22 02:53:10,016537

Right now is

SQL> select sysdate from dual;

SYSDATE
-------------------
19.09.2022 13.17:07

Query:

SQL> select id,
  2         round (
  3            sum (
  4               (cast (act_taken_on as date) - cast (created_on as date)) * 24)
  5               over (partition by id order by created_on),
  6            0) aging,
  7         round ((cast (due_dt as date) - sysdate) * 24, 0) time_left
  8    from test;

 ID      AGING  TIME_LEFT
--- ---------- ----------
  1         24         38
  1         48         38
  2         24         38

SQL>

CodePudding user response：

Convert your TIMESTAMP values to DATEs, then subtract them and take the FLOOR (that is, round down).

When you subtract one DATE from another you get a floating-point value that's the difference between them in calendar days.

Like this:

FLOOR(24*(CAST(due_dt AS DATE) - CAST(created_on AS DATE)))