0

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.

VJS
  • 2,891
  • 7
  • 38
  • 70
  • Any attempts on your own? – Gnqz Jun 14 '22 at 07:07
  • Does this answer your question? [Calculating difference between two timestamps in Oracle in milliseconds](https://stackoverflow.com/questions/11617962/calculating-difference-between-two-timestamps-in-oracle-in-milliseconds) – Gnqz Jun 14 '22 at 07:09

2 Answers2

0

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0
SELECT (END_DT - START_DT) * 60 * 60 * 24 FROM MY_TABLE;    -- Seconds
SELECT (END_DT - START_DT) * 60 * 24 FROM MY_TABLE;         -- Minutes
SELECT (END_DT - START_DT) * 24 FROM MY_TABLE;              -- Hours

To get more info refer to this link: Date Difference

Mammad Yahyayev
  • 52
  • 1
  • 7