Sample Header Ad - 728x90

Function to compute difference between 2 times

1 vote
1 answer
458 views
I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I have commented out seconds, just working on minutes.
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;
$$;
Example: 01:15:00 - 02:00:00 should give me 45 minutes, so I do the following and I get the correct answer.
select * from time_diff(1, 15, 0, 2);
However, if I do this: 23:15:00 - 01:00:00 - the should give me 105 minutes (60 + 45).
select * from time_diff(23, 15, 0, 1);
But the result I am getting is -1335. I am trying to work out where I have gone wrong here. Also I am invoking DATE_PART functions, this seems to be a quite an expensive process in terms of CPU usage. Is there a better way of optimising this function. With the first example I am getting results in 0.007s on 2018 i7 Mac mini. Although I do think this function is quick, but could it be better?
Asked by Tomus85 (13 rep)
Mar 21, 2021, 02:34 PM
Last activity: Mar 22, 2021, 10:45 PM