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

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

如何计算Oracle中的两个时间戳之间的毫秒差?

8 个解决方案

#1

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.

当您减去类型为TIMESTAMP的两个变量时，您将得到一个间隔日到秒，其中包括根据平台而定的毫秒数和/或微秒数。如果数据库在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

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

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.

但是，通常情况下，将间隔日设置为第二个表示，或者在数小时、分钟、秒等中设置单独的列，而不是计算两个时间戳值之间的总毫秒数，这更有用。

#2

Here's a stored proc to do it:

下面是一个存储的proc:

CREATE OR REPLACE function timestamp_diff(a timestamp, b timestamp) return number is 
begin
  return extract (day    from (a-b))*24*60*60 +
         extract (hour   from (a-b))*60*60+
         extract (minute from (a-b))*60+
         extract (second from (a-b));
end;
/

Up Vote if you also wanted to beat the crap out of the Oracle developer who negated to his job!

如果你还想打败那些对他的工作持否定态度的Oracle开发人员，那就投票吧!

BECAUSE comparing timestamps for the first time should take everyone an hour or so...

因为第一次比较时间戳需要每个人花一个小时左右的时间……

#3

Easier solution:

SELECT numtodsinterval(date1-date2,'day') time_difference from dates;

For timestamps:

SELECT (extract(DAY FROM time2-time1)*24*60*60)+ 
(extract(HOUR FROM time2-time1)*60*60)+
(extract(MINUTE FROM time2-time1)*60)+
extract(SECOND FROM time2-time1)
into diff FROM dual;

RETURN diff;

#4

Select date1 - (date2 - 1) * 24 * 60 *60 * 1000 from Table;

从表中选择date1 - (date2 - 1) * 24 *60 *60 * 1000;

#5

Better to use procedure like that:

最好使用这样的程序:

CREATE OR REPLACE FUNCTION timestamp_diff
(
start_time_in TIMESTAMP
, end_time_in TIMESTAMP
)
RETURN NUMBER
AS
l_days NUMBER;
l_hours NUMBER;
l_minutes NUMBER;
l_seconds NUMBER;
l_milliseconds NUMBER;
BEGIN
SELECT extract(DAY FROM end_time_in-start_time_in)
, extract(HOUR FROM end_time_in-start_time_in)
, extract(MINUTE FROM end_time_in-start_time_in)
, extract(SECOND FROM end_time_in-start_time_in)
INTO l_days, l_hours, l_minutes, l_seconds
FROM dual;

l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000;
RETURN l_milliseconds;

END;

You can check it by calling:

你可致电:

SELECT timestamp_diff (TO_TIMESTAMP('12.04.2017 12:00:00.00', 'DD.MM.YYYY HH24:MI:SS.FF'), 
                      TO_TIMESTAMP('12.04.2017 12:00:01.111', 'DD.MM.YYYY HH24:MI:SS.FF')) 
            as milliseconds
    FROM DUAL;

#6

The timestamp casted correctly between formats else there is a chance the fields would be misinterpreted.

时间戳在格式之间被正确地赋值，否则字段可能会被误解。

Here is a working sample that is correct when two different dates (Date2, Date1) are considered from table TableXYZ.

当表TableXYZ中考虑两个不同的日期(Date2, Date1)时，这个工作示例是正确的。

SELECT ROUND (totalSeconds / (24 * 60 * 60), 1) TotalTimeSpendIn_DAYS,
       ROUND (totalSeconds / (60 * 60), 0) TotalTimeSpendIn_HOURS,
       ROUND (totalSeconds / 60) TotalTimeSpendIn_MINUTES,
       ROUND (totalSeconds) TotalTimeSpendIn_SECONDS
  FROM (SELECT ROUND (
                    EXTRACT (DAY FROM timeDiff) * 24 * 60 * 60
                  + EXTRACT (HOUR FROM timeDiff) * 60 * 60
                  + EXTRACT (MINUTE FROM timeDiff) * 60
                  + EXTRACT (SECOND FROM timeDiff))
                  totalSeconds,
          FROM (SELECT TO_TIMESTAMP (
                            TO_CHAR (Date2,
                                     'yyyy-mm-dd HH24:mi:ss')
                          - 'yyyy-mm-dd HH24:mi:ss'),
                       TO_TIMESTAMP (
                          TO_CHAR (Date1,
                                   'yyyy-mm-dd HH24:mi:ss'),
                          'yyyy-mm-dd HH24:mi:ss')
                          timeDiff
                  FROM TableXYZ))

#7

Above one has some syntax error, Please use following on oracle:

上面有一些语法错误，请在oracle上使用以下代码:

SELECT ROUND (totalSeconds / (24 * 60 * 60), 1) TotalTimeSpendIn_DAYS,
  ROUND (totalSeconds      / (60 * 60), 0) TotalTimeSpendIn_HOURS,
  ROUND (totalSeconds      / 60) TotalTimeSpendIn_MINUTES,
  ROUND (totalSeconds) TotalTimeSpendIn_SECONDS
FROM
  (SELECT ROUND ( EXTRACT (DAY FROM timeDiff) * 24 * 60 * 60 + EXTRACT (HOUR FROM timeDiff) * 60 * 60 + EXTRACT (MINUTE FROM timeDiff) * 60 + EXTRACT (SECOND FROM timeDiff)) totalSeconds
  FROM
    (SELECT TO_TIMESTAMP(TO_CHAR( date2 , 'yyyy-mm-dd HH24:mi:ss'), 'yyyy-mm-dd HH24:mi:ss') - TO_TIMESTAMP(TO_CHAR(date1, 'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd HH24:mi:ss') timeDiff
    FROM TABLENAME
    )
);

#8

I) if you need to calculate the elapsed time in seconds between two timestamp columns try this :

I)如果您需要计算两个时间戳列之间的流逝时间(以秒为单位)，请尝试以下操作:

SELECT extract ( day from (end_timestamp - start_timestamp) )*86400 + extract ( hour from (end_timestamp - start_timestamp) )*3600 + extract ( minute from (end_timestamp - start_timestamp) )*60 + extract ( second from (end_timestamp - start_timestamp) ) FROM table_name

选择extract (day from (end_timestamp - start_timestamp))*86400 +提取(时间从(end_timestamp - start_timestamp))*3600 +提取(分钟from (end_timestamp - start_timestamp))*60 +提取(从表_name中提取的第二个from (end_timestamp - start_timestamp))。

II) if u wanna just show the time difference in character format try this:

II)如果你只想显示字符格式的时差，请尝试以下方法:

SELECT to_char (end_timestamp - start_timestamp) FROM table_name

从table_name中选择to_char (end_timestamp - start_timestamp)