sql - 计算Oracle中两个时间戳之间的差异(以毫秒为单位)

Question

如何计算 Oracle 中两个时间戳之间的时间差(以毫秒为单位)？

Answer 1

当您减去两个TIMESTAMP类型的变量时，您会得到一个INTERVAL DAY TO SECOND，其中包括毫秒和/或微秒数，具体取决于平台。如果数据库在 Windows 上运行，systimestamp 通常为毫秒。如果数据库在 Unix 上运行，systimestamp 通常会有微秒。

  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

您可以使用EXTRACT函数提取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

然后，您可以将每个组件转换为毫秒并将它们相加

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

但是，通常情况下，使用 INTERVAL DAY TO SECOND 表示法或使用单独的小时、分钟、秒等列比计算之间的总毫秒数更有用两个 TIMESTAMP 值。