Oracle中比较带毫秒日期大小,sql – 计算Oracle中两个时间戳之间的差异(以毫秒为单位)...

当减去类型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值之间的总毫秒数更为有用。