SQL ORACLE, How to subtract sysdate?

June 2, 2023

How can I subtract the system date from the created task date in SQL ORACLE to get the difference in days, hours, minutes?

I tried this code but i get wrong values:
TO_CHAR(TO_DATE('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + (sysdate - a.CREATE_DATE_PL ), 'dd:hh24:mi:ss') AS TT_LIFETIME

My results is below (the report has been generated 02.06 14:05

enter image description here

>Solution :

Difference of two DATE datatype values is number of days between them, so you have to do some arithmetic to extract days/hours/minutes, or – another option – to try extract with numtodsinterval.

For example:

SQL> WITH
  2     test (create_date_pl)
  3     AS
  4        (SELECT TO_DATE ('01.06.2023 08:10', 'dd.mm.yyyy hh24:mi') FROM DUAL)
  5  SELECT SYSDATE,
  6         create_date_pl,
  7         --
  8         SYSDATE - create_date_pl diff,
  9         NUMTODSINTERVAL (SYSDATE - create_date_pl, 'day') c1,
 10         EXTRACT (DAY FROM NUMTODSINTERVAL (SYSDATE - create_date_pl, 'day')) cd,
 11         EXTRACT (HOUR FROM NUMTODSINTERVAL (SYSDATE - create_date_pl, 'day')) ch,
 12         EXTRACT (MINUTE FROM NUMTODSINTERVAL (SYSDATE - create_date_pl, 'day')) cm
 13    FROM test;

SYSDATE          CREATE_DATE_PL         DIFF C1                                               CD         CH         CM
---------------- ---------------- ---------- ---------------------------------------- ---------- ---------- ----------
02.06.2023 14:29 01.06.2023 08:10 1,26333333 +000000001 06:19:12.000000000                     1          6         19

SQL>

When I ran that code, it was 2th of June 2023 at 14:29. Date to be subtracted was 1st of June 2023 (yesterday) at 08:10.

diff represents number of days between those two values
c1 is what numtodsinterval returns
cd, ch and cm contain extracted number of days/hours/minutes, i.e. difference is 1 day, 6 hours and 19 minutes