How to extract timestamp differences in hours and do cumulative sum

Vicky 0
Viewed 27
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   |
+----+---------+-----------+
oracle sql
2 Answers

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>

As you commented that you'd want to include CASE expression that regards the status column (but you're getting some errors), I'm not sure what exactly you meant to do with that, but - here's how (time_left has changed as sysdate now returns 20.09.2022 07:02):

SQL> select id,
  2         sum (
  3            case
  4               when status = 'created'
  5               then
  6                    (cast (act_taken_on as date) - cast (created_on as date))
  7                  * 24
  8               when status = 'Approved'
  9               then
 10                    (cast (act_taken_on as date) - cast (created_on as date))
 11                  * 24
 12            end)
 13            over (partition by id order by created_on) aging,
 14         round ((cast (due_dt as date) - sysdate) * 24, 0) time_left
 15    from test;

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

SQL>
Littlefoot0

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)))
O. Jones0
Related