I want to create a simple function in Postgres to find the difference between 2
TIME – not
TIMESTAMP. As show 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?