我有一個函式可以計算 2 個日期或時間戳之間的差異，它作業正常。
有沒有辦法修改函式以顯示差異中 TIMESTAMP 的小數部分作為結果的一部分。如果可能的話，我希望這兩種情況都在同一個函式中處理。
CREATE OR REPLACE FUNCTION datediff (p_from date, p_to date)
return varchar2 is
l_years PLS_INTEGER;
l_from DATE;
l_interval interval day(3) to second(0);
begin
l_years := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
l_from := ADD_MONTHS(p_from, l_years * 12);
l_interval := (p_to - l_from) DAY(3) TO SECOND(0);
return l_years || ' Years '
|| extract (day from l_interval) || ' Days '
|| extract (hour from l_interval) || ' Hours '
|| extract (minute from l_interval) || ' Minutes '
|| extract (second from l_interval) || ' Seconds';
end datediff;
/
SELECT
datediff( TO_DATE('1981-04-01 10:11:13','YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2022-04-03 17:48:09','YYYY-MM-DD HH24:MI:SS')) as diff FROM DUAL;
DIFF
41 Years 2 Days 7 Hours 36 Minutes 56 Seconds
SELECT
datediff (TO_TIMESTAMP('1981-04-01 10:11:13.551000000', 'YYYY-MM-DD HH24:MI:SS.FF'),
TO_TIMESTAMP('2022-04-03 17:48:09.878700000', 'YYYY-MM-DD HH24:MI:SS.FF')) as diff FROM DUAL;
/* want to show fractional difference here */
DIFF
41 Years 2 Days 7 Hours 36 Minutes 56 Seconds
uj5u.com熱心網友回復：
您想將TIMESTAMP引數傳遞給函式，并且當您添加年份時，您還需要確保傳播時間戳的小數部分（因為ADD_MONTHS回傳DATE沒有小數秒的資料型別）：
CREATE OR REPLACE FUNCTION datediff (p_from timestamp, p_to timestamp)
return varchar2 is
l_years PLS_INTEGER;
l_from TIMESTAMP;
l_interval interval day(3) to second(6);
begin
l_years := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
l_from := CAST(TRUNC(ADD_MONTHS(p_from, l_years * 12), 'MI') AS TIMESTAMP)
NUMTODSINTERVAL( EXTRACT(SECOND FROM p_from), 'SECOND' );
l_interval := (p_to - l_from) DAY(3) TO SECOND(6);
return l_years || ' Years '
|| extract (day from l_interval) || ' Days '
|| extract (hour from l_interval) || ' Hours '
|| extract (minute from l_interval) || ' Minutes '
|| extract (second from l_interval) || ' Seconds';
end datediff;
/
然后：
SELECT datediff(
TIMESTAMP '1981-04-01 10:11:13.551000000',
TIMESTAMP '2022-04-03 17:48:09.878700000'
) as diff
FROM DUAL;
輸出：
| 差異 |
|---|
| 41 年 2 天 7 小時 36 分鐘 56.3277 秒 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/506824.html
上一篇：我不能將“繼續”命令放在定義中