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

2021-12-05 00:00:00 datetime sql oracle

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

How do I calculate the time difference in milliseconds between two timestamps in Oracle?

推荐答案

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

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

您可以使用 EXTRACT 函数来提取 INTERVAL DAY TO SECOND 的各个元素

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

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

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.

相关文章