Oracle : how to subtract two dates and get seconds of the result-CodePudding

I have created oracle NVL function which would subtract start and end time column and give the result

However I am not getting correct output value

Example


Start time- 2:33:10 

End time -2:33:20 

Actual Output - 9 seconds

Expected Output- 10 seconds

Query

NVL(TO_CHAR( table.time1,'YYYY/MM/DD HH24:MI:SS'),' ')"Time1",
NVL(TO_CHAR( table.time2,'YYYY/MM/DD HH24:MI:SS'),' ')"Time2",
NVL(dbms_lob.substr(oprm.message,4000),' ') AS "Messages",
NVL(REGEXP_SUBSTR (CAST( table.time2 AS TIMESTAMP) - CAST( table.time1 AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration

CodePudding user response：

Your data type is most probably TIMESTAMP that would explain the rounding problem.

You may workaround it by first casting to DATE (to get rid of the milliseconds) and that casting it back to TIMESTAMP (to be able to perform your regexp_substr)

This sample data replays your problem

select opa.*, 
NVL(REGEXP_SUBSTR (CAST(opa.end_time AS TIMESTAMP) - CAST(opa.start_time AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration,
NVL(REGEXP_SUBSTR (CAST(CAST(opa.end_time AS DATE)AS TIMESTAMP) - CAST(CAST(opa.start_time AS DATE)AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration2
from tab opa;

START_TIME                           END_TIME                             DURATION                    DURATION2                   
------------------------------------ ------------------------------------ --------------------------- ---------------------------
04.05.2021 09:13:07,555000000  02:00 04.05.2021 09:13:18,111000000  02:00 00:00:10                    00:00:11

CodePudding user response：

Let me see if I can help shed some light on your situation. First lets create a table

-- format the date in my current session.--
alter session set nls_date_format = 'DD-MON-YYYY HH12:MI:SS PM';

 CREATE TABLE TIME_HOLDER 
    (
      CREATED_DATE DATE 
    , SECOND_DATE DATE 
    )

add some records to that table to start calculating the difference.

SET DEFINE OFF;
Insert into TIME_HOLDER (CREATED_DATE,SECOND_DATE) values (to_date('02-NOV-2021 02:01:45 PM','DD-MON-YYYY HH12:MI:SS PM'),to_date('22-NOV-2021 02:01:52 PM','DD-MON-YYYY HH12:MI:SS PM'));

Now to start calculating the difference between the two dates using two date columns you would have to use something like the following.

select     extract (day from numtodsinterval (second_date
                                                   - add_months (created_date,
                                                                 floor (months_between (second_date,created_date))),
                                                   'day'))
             || ' days - '
             || extract (hour from numtodsinterval (second_date
                                                    - add_months (created_date,
                                                                  floor (months_between (second_date,created_date))),
                                                    'day'))
             || ':'
             || extract (minute from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      'day'))
             || ':'
             || extract (second from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      'day')) as time_diff
     from   TIME_HOLDER

First you could calculate the interval between these two dates and after that export all the data you need from that interval: hopefully this example would help to shed some light on how to go about doing something like this in oracle.