CODEWITHSUNDEEP
sqloracle-databaseselectinserttimestamp
JLearner
JLearner

Reputation: 1311

Any ways to calculate exact time between two timestamp with timezone

is there any way in oracle to calculate the difference between two time stamps value with time zones in oracle sql plus as it is not possible to use timestampdiff or datediff inside oracle.

Thanks

Upvotes: 1

Views: 1512

Answers (2)

HAL 9000
HAL 9000

Reputation: 3985

Consider the following in case you need the number of milliseconds between two timestamps:

  create or replace Function msecBetween
   (ts1 timestamp with time zone,
    ts2 timestamp with time zone,
    numDec number default 0
   )
  Return Number is
    i INTERVAL DAY(3) TO SECOND(3) := ts2 - ts1;
  Begin
    return round (
      +     extract( day    from i )*24*60*60*1000
      +     extract( hour   from i )*60*60*1000
      +     extract( minute from i )*60*1000
      +     extract( second from i )*1000
    , numDec);
  End;

Upvotes: 1

Gaius
Gaius

Reputation: 2595

Just subtract them:

SQL> select (systimestamp + 1) - systimestamp from dual;

(SYSTIMESTAMP+1)-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 23:59:59.884789

Upvotes: 4

Related Questions