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

发布于07月24日

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

推荐答案

当你减go 两个类型为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值之间的总毫秒数更有用.