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?
exp_hr
is always ahead in time and never wraps around (> 24h)?exp_hr
never exceeds 24. However, I can see in coding that you've wrapped this. Very handy.