How to substract 2 varchar dates in oracle?-CodePudding

I have these varchar : 20211026231735.

So I would like a query to substract actual sysdate to that date and convert the substraction to DAY HOURS AND SECONDS.

select TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') - start_time from TABLEA where job_name='jOB_AA_BB';
I get 4220.

Any help please? Thanks

CodePudding user response：

When you do datetime arithmetic with the DATE datatype, you get back a NUMBER of days. To get an INTERVAL you can subtract two TIMESTAMPs. You don't say what the data type is for start_time, but you might get away with this:

select localtimestamp - start_time 
from tablea where job_name='jOB_AA_BB';

LOCALTIMESTAMP gives you a TIMESTAMP value in the current session time zone. There's also CURRENT_TIMESTAMP, which give you the same thing in a TIMESTAMP WITH TIME ZONE and SYSTIMESTAMP that gives you the database time in TIMESTAMP WITH TIME ZONE. You may need to convert your start_time to avoid time zone differences, if any.

CodePudding user response：

You can us the function numtodsinterval to convert the results of date arithmetic to an interval. If necessary then use extract to pull out the needed components.

with tablea(job_name, start_time) as 
     (select 'jOB_AA_BB','20211026231735' from dual)
select numtodsinterval((SYSDATE - to_date( start_time,'yyyymmddhh24miss')),'hour') date_diff
 from tablea where job_name='jOB_AA_BB' ;

with tablea(job_name, start_time) as 
     (select 'jOB_AA_BB','20211026231735' from dual) 
select extract (hour from date_diff) || ':' || extract (minute from date_diff)
  from ( 
         select numtodsinterval((sysdate - to_date( start_time,'yyyymmddhh24miss')),'day') date_diff
           from tablea where job_name='jOB_AA_BB' 
       );

NOTE: I am not sure how you got any result, other than an error, as your query winds up as a string - a string. You should not convert sysdate to a string but your string to a date (better yet store it as the proper data type - date).

CodePudding user response：

You can convert the value to a date (rather than converting SYSDATE to a string) and then subtract and explicitly return the value as an INTERVAL DAY TO SECOND type:

SELECT (SYSDATE - TO_DATE('20211026231735', 'YYYYMMDDHH24MISS')) DAY TO SECOND
FROM   DUAL;

Or, for your table:

SELECT (SYSDATE - TO_DATE(start_time,'YYYYMMDDHH24MISS') DAY TO SECOND
FROM   TABLEA
WHERE  job_name='jOB_AA_BB';