Difference between 2 times in sec in Pl/SQL

I need some help to identify difference between 2 times in sec

variable :

current_time : this is current time

check_begin_time : in YYYYMMDDHH24MMSS format

I need to check the difference i.e. current time - check_begin_time in secs

Thanks.

>Solution :

As difference of two DATE datatype values in Oracle represents number of days between them, you have to multiply it by 24 (as there are 24 hours in a day) and 60 (as there are 60 minutes in an hour) and 60 (as there are 60 seconds in a minute).

So:

(setting date format, just to know what is what; you don’t have to do that)

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

I presume your value is stored as a string (according to date format you posted; though, it is wrong – minutes are MI, not MM) so first convert it to a DATE datatype value, and then do the calculation:

SQL> select sysdate now,
  2    (sysdate - to_date('20220614090500', 'yyyymmddhh24miss')) * 24 * 60 * 60 seconds
  3  from dual;

NOW                    SECONDS
------------------- ----------
14.06.2022 09:09:54        294

SQL>