Difference Between Timestamps in Milliseconds in Oracle

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.

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

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;

How to trim milliseconds of a timestamp field in oracle

You might try the following:

CREATE VIEW myview AS
SELECT <other_columns>
     , CAST(crte_dtm AS TIMESTAMP(3)) AS crte_dtm
     , CAST(end_dtm AS TIMESTAMP(3)) AS end_dtm
  FROM employee;

Hope this helps.

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