計算2次之差的函式

TIME我想在 Postgres 中創建一個簡單的函式來找出 2 -not之間的區別TIMESTAMP。如下圖，它接受4個參數：小時、分鐘、秒和過期（小時）。在這個例子中，我已經註釋掉了秒，只在分鐘上工作。

CREATE OR REPLACE FUNCTION time_diff(hr INT, min INT, sec INT, exp_hr INT)
RETURNS INT
LANGUAGE plpgsql AS
$$
DECLARE
   cur_time    TIME;
   expire_time TIME;

   diff_interval INTERVAL;
   diff INT = 0;
BEGIN
   cur_time    = CONCAT(hr,  ':',  min, ':', sec) AS TIME; -- cast hour, minutes and seconds to TIME
   expire_time = CONCAT(exp_hr, ':00:00') AS TIME;         -- cast expire hour to TIME

   -- MINUS operator for TIME returns interval 'HH:MI:SS;
   diff_interval = expire_time - cur_time;

   diff = DATE_PART('hour', diff_interval);

   diff = diff * 60 + DATE_PART('minute', diff_interval);

   --diff = diff * 60 + DATE_PART('second', diff_interval);

   RETURN diff;
END;
$$;

範例：01:15:00 - 02:00:00 應該給我 45 分鐘，所以我執行以下操作並得到正確答案。

select * from time_diff(1, 15, 0, 2);

但是，如果我這樣做：23:15:00 - 01:00:00 - 應該給我 105 分鐘 (60 + 45)。

select * from time_diff(23, 15, 0, 1);

但我得到的結果是-1335。我正在努力找出我在這裡出錯的地方。

我也在呼叫DATE_PART函式，就 CPU 使用而言，這似乎是一個相當昂貴的過程。有沒有更好的方法來優化這個功能。通過第一個範例，我0.007s在 2018 i7 Mac mini 上獲得了結果。雖然我確實認為這個功能很快，但它會更好嗎？

假設exp_hr總是在時間上領先，但永遠不會超過 24 小時。

為了簡單起見，我建議返回一個interval。然後它可以工作小時、分鐘、秒、微秒等。

除了正確之外，這應該更快幾個數量級：

CREATE OR REPLACE FUNCTION f_time_diff(hr INT, min INT, sec INT, exp_hr INT)
 RETURNS interval
 LANGUAGE sql PARALLEL SAFE IMMUTABLE AS
$func$
SELECT CASE WHEN hr >= exp_hr       -- wrap around midnight
           THEN 24 + exp_hr - hr
           ELSE exp_hr - hr
           END * interval '1 hour'
               - interval '1 min' * min
               - interval '1 sec' * sec;
$func$;

請注意，這將返回 24h for input = output exactly。對於極端情況，您可能會做出其他決定。

展示：

test=*> SELECT f_time_diff( 1, 15, 0, 2) AS example1
test-*>      , f_time_diff(23, 15, 0, 1) AS example2
test-*>      , f_time_diff( 1, 15, 0, 1) AS example3
test-*>      , f_time_diff( 1,  0, 0, 1) AS example4;
example1 | example2 | example3 | example4 
----------+----------+----------+----------
00:45:00 | 01:45:00 | 23:45:00 | 24:00:00
(1 row)

如果您實際上需要分鐘數integer（如果有，則截斷秒數），請提取紀元並使用整數除法：

SELECT EXTRACT(epoch FROM f_time_diff(23,15,0,1))::int / 60 AS example2;  -- 105

（或將其建構到函式中。）

db<>在這裡擺弄

現在最昂貴的部分是類型轉換。如果您輸入time/ interval，它會變得更快。或者更好：timestamp或者timestamptz，然後它變得微不足道，你不需要一個函式來開始……

然而，在轉換為之前連接字元串time（就像你有它一樣）要貴得多。

引用自：https://dba.stackexchange.com/questions/287436