0

I have a table with two columns, Start_time and SLA.

Start time updates for each day and is in a date format e.g., 01-Jun-2021 19:15:38

SLA column is having fixed HH24MI as 2010

I want 1915 - 2010 to be -00:55 (as in HH24MI format)

SELECT TO_CHAR((TO_CHAR(START_TIME,'HH24')||TO_CHAR(START_TIME,'MI'))-2010,'0000') 
FROM DUAL;

Above will give the result as -0095 but I want it to be -00:55

MT0
  • 143,790
  • 11
  • 59
  • 117
Pritam
  • 5
  • 2
  • @garymyers - I looked a few times at the thread you linked this one to, and I don't see anywhere where they show how to present the result as an interval hour to minute (especially one that may be negative). Care to enlighten us? –  Jun 03 '21 at 03:21
  • It was a use TIMESTAMPs as TIMESTAMP maths returns INTERVALs and you use the EXTRACT to get the components from the INTERVAL. It was explained very well in the linked question but someone removed that link and contributed nothing to the discussion – Gary Myers Jun 03 '21 at 05:48

1 Answers1

0

Store the start_time as a DATE data type and the sla as an INTERVAL DAY TO SECOND data type:

CREATE TABLE table_name (
  start_time DATE,
  sla        INTERVAL DAY TO SECOND
);

Then your data would be:

INSERT INTO table_name ( start_time, sla ) VALUES (
  TO_DATE('01-Jun-2021 19:15:38', 'DD-MON-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE=American'),
  INTERVAL '20:10:00' HOUR TO SECOND
);

And, to find the difference, you can use:

SELECT start_time,
       sla,
       (start_time - TRUNC(start_time)) DAY TO SECOND - sla AS difference
FROM   table_name

Which outputs:

START_TIME SLA DIFFERENCE
2021-06-01 19:15:38 +00 20:10:00.000000 -000000000 00:54:22.000000000

If you want the output as a formatted string, rather than as an interval, then:

SELECT start_time,
       sla,
       CASE WHEN difference < INTERVAL '0' HOUR THEN '-' END
       || TO_CHAR( ABS( EXTRACT( HOUR FROM difference ) ), 'FM00' )
       || TO_CHAR( ABS( EXTRACT( MINUTE FROM difference ) ), 'FM00' )
         AS difference
FROM   (
  SELECT start_time,
         sla,
         (start_time - TRUNC(start_time)) DAY TO SECOND - sla AS difference
  FROM   table_name
)

Which outputs:

START_TIME SLA DIFFERENCE
2021-06-01 19:15:38 +00 20:10:00.000000 -0054

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Nice use for intervals. You might even add the result as a virtual column i.e. `target_completion generated always as (start_time + sla)`. – William Robertson Jun 03 '21 at 19:34