我創建oracle NVL function了它將減去開始和結束時間列并給出結果
但是我沒有得到正確的輸出值
例子
Start time- 2:33:10
End time -2:33:20
Actual Output - 9 seconds
Expected Output- 10 seconds
詢問
NVL(TO_CHAR( table.time1,'YYYY/MM/DD HH24:MI:SS'),' ')"Time1",
NVL(TO_CHAR( table.time2,'YYYY/MM/DD HH24:MI:SS'),' ')"Time2",
NVL(dbms_lob.substr(oprm.message,4000),' ') AS "Messages",
NVL(REGEXP_SUBSTR (CAST( table.time2 AS TIMESTAMP) - CAST( table.time1 AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration
uj5u.com熱心網友回復：
您的資料型別最有可能 TIMESTAMP解釋舍入問題。
您可以解決辦法 通過先澆鑄到DATE（擺脫毫秒）和鑄造回TIMESTAMP（到能夠執行你的REGEXP_SUBSTR）
此示例資料重播您的問題
select opa.*,
NVL(REGEXP_SUBSTR (CAST(opa.end_time AS TIMESTAMP) - CAST(opa.start_time AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration,
NVL(REGEXP_SUBSTR (CAST(CAST(opa.end_time AS DATE)AS TIMESTAMP) - CAST(CAST(opa.start_time AS DATE)AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration2
from tab opa;
START_TIME END_TIME DURATION DURATION2
------------------------------------ ------------------------------------ --------------------------- ---------------------------
04.05.2021 09:13:07,555000000 02:00 04.05.2021 09:13:18,111000000 02:00 00:00:10 00:00:11
uj5u.com熱心網友回復：
讓我看看我是否可以幫助闡明您的情況。首先讓我們創建一個表
-- format the date in my current session.--
alter session set nls_date_format = 'DD-MON-YYYY HH12:MI:SS PM';
CREATE TABLE TIME_HOLDER
(
CREATED_DATE DATE
, SECOND_DATE DATE
)
向該表添加一些記錄以開始計算差異。
SET DEFINE OFF;
Insert into TIME_HOLDER (CREATED_DATE,SECOND_DATE) values (to_date('02-NOV-2021 02:01:45 PM','DD-MON-YYYY HH12:MI:SS PM'),to_date('22-NOV-2021 02:01:52 PM','DD-MON-YYYY HH12:MI:SS PM'));
現在要開始使用兩個日期列計算兩個日期之間的差異，您必須使用如下所示的內容。
select extract (day from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date,created_date))),
'day'))
|| ' days - '
|| extract (hour from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date,created_date))),
'day'))
|| ':'
|| extract (minute from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date, created_date))),
'day'))
|| ':'
|| extract (second from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date, created_date))),
'day')) as time_diff
from TIME_HOLDER
首先，您可以計算這兩個日期之間的間隔，然后從該間隔匯出您需要的所有資料：希望這個示例有助于闡明如何在 oracle 中執行此類操作。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/357913.html