计算Oracle中两个时间戳之间的差异(以毫秒为单位) [英] Calculating difference between two timestamps in Oracle in milliseconds
问题描述
如何计算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.
这篇关于计算Oracle中两个时间戳之间的差异(以毫秒为单位)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!