oracle表相减mis,以毫秒為單位計算Oracle中的兩個時間戳之間的差異

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

如何計算Oracle中的兩個時間戳之間的毫秒差?

8 个解决方案

#1

61

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

17

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

11

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

1

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

從表中選擇date1 - (date2 - 1) * 24 *60 *60 * 1000;

#5

0

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

-1

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

-1

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

-1

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)