Calculating Difference Between Two Timestamps in Oracle in Milliseconds

Calculating difference between two timestamps in Oracle in milliseconds

When you subtract two variables of type TIMESTAMP, you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds. If the database is running on Unix, systimestamp will generally have microseconds.

  1  select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' )
  2*   from dual
SQL> /

SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD')
---------------------------------------------------------------------------
+000000000 14:51:04.339000000

You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND

SQL> ed
Wrote file afiedt.buf

  1  select extract( day from diff ) days,
  2         extract( hour from diff ) hours,
  3         extract( minute from diff ) minutes,
  4         extract( second from diff ) seconds
  5    from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff
  6*           from dual)
SQL> /

      DAYS      HOURS    MINUTES    SECONDS
---------- ---------- ---------- ----------
         0         14         55     37.936

You can then convert each of those components into milliseconds and add them up

SQL> ed
Wrote file afiedt.buf

  1  select extract( day from diff )*24*60*60*1000 +
  2         extract( hour from diff )*60*60*1000 +
  3         extract( minute from diff )*60*1000 +
  4         round(extract( second from diff )*1000) total_milliseconds
  5    from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff
  6*           from dual)
SQL> /

TOTAL_MILLISECONDS
------------------
          53831842

Normally, however, it is more useful to have either the INTERVAL DAY TO SECOND representation or to have separate columns for hours, minutes, seconds, etc. rather than computing the total number of milliseconds between two TIMESTAMP values.

Average Timestamp oracle with milliseconds

You may use EXTRACT to get AVG seconds.

SELECT AVG (EXTRACT (SECOND FROM (sva.endTime - sva.startTime)))
          AS avg_seconds
  FROM SVATable sva;

Difference Between Timestamps in Milliseconds in Oracle

select
  extract(second from systimestamp - doj) * 1000
from
  test1;

get the date difference in milliseconds in oracle

SELECT ((extract(DAY FROM time2-time1)*24*60*60)+ 
(extract(HOUR FROM time2-time1)*60*60)+
(extract(MINUTE FROM time2-time1)*60)+
extract(SECOND FROM time2-time1)) *1000
as millisecs FROM dual;

can be done using above approach

Difference between 2 times in sec in Pl/SQL

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>

ORACLE sql hours between two timestamps format

Just use a subquery to extract the hours from the expression

SELECT  extract( hour from x) hours FROM
(SELECT (date1 - date2)x FROM B)

Time difference between two timestamps in Apex 5.1

Subtracting two date values in Oracle results in number of DAYS. Therefore, you'd have to do some math in order to get hours (or minutes). Here's an example:

SQL> col diff_days format 90D00000
SQL> col diff_hours format 90D00000
SQL> col diff_minutes format 90D00000
SQL>
SQL> with test as (select to_date('29.12.2017 16:05', 'dd.mm.yyyy hh24:mi') d_order,
  2                       to_date('29.12.2017 16:39', 'dd.mm.yyyy hh24:mi') d_now
  3                from dual)
  4  select
  5     d_now - d_order              diff_days,
  6    (d_now - d_order) * 24        diff_hours,
  7    (d_now - d_Order) * (24 * 60) diff_minutes
  8  from test;

DIFF_DAYS DIFF_HOURS DIFF_MINUTES
--------- ---------- ------------
  0,02361    0,56667     34,00000

SQL>

You'd do similarly: D_NOW would be SYSDATE, while D_ORDER would be a value stored in a(n ORDERS) table.

How to find elapsed time in milliseconds by compare two timestamps in java

Get your updated timestamp as a date-time object from the database. Add two minutes to obtain the timeout time. Compare to the current time.

    ResultSet rs = yourPreparedStatement.executeQuery();
    if (rs.next()) {
        OffsetDateTime odt = rs.getObject("your_db_column", OffsetDateTime.class);
        OffsetDateTime currentTime = OffsetDateTime.now(odt.getOffset());
        OffsetDateTime timeoutOdt = odt.plus(TIMEOUT);
        if (currentTime.isAfter(timeoutOdt)) {
            System.out.println("Timed out");
        }
        else {
            System.out.println("Not timed out yet");
        }
    }

I have assumed a TIMEOUT constant of type Duration. That's flexible in that it will allow you to define the timeout in minutes or milliseconds or which unit you prefer. For example:

private static final String CONFIGUTRED_TIMEOUT = "PT2M"; // get from configuration
private static final Duration TIMEOUT = Duration.parse(CONFIGUTRED_TIMEOUT);

The configured timeout of PT2M may look funny. Read as a period of time of 2 minutes. The format is ISO 8601. You may alternatively use for example Duration.ofMinutes(2) or Duration.ofMillis(120_000).

If the datatype in your Oracle database is timestamp with time zone, which is recommended, you should be able to retrieve it as an OffsetDateTime as shown. You may also try ZonedDateTime or Instant. If the column in the database hasn't got any time zone, retrieve as LocalDateTime and convert to the proper time zone using its atZone method.

The date and time classes you tried to use, Date, DateFormat, SimpleDateFormat, TimeZone and Timestamp, are all poorly designed and all long outdated. Avoid using those.

---

Related Topics