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?

Thanks