Calculate time difference in PSQL with HH:MM splitted in several columns-CodePudding

I've a PSQL table like this:

Order	Start_Hour	Start_Minute	Finish_Hour	Finish_Minute
10	10	15	12	15
10	12	15	14	15
10	16	00	17	00

And I need to calculate by a query the total time expressed in hours that I spent to finish the order. In this scenario I expect to have a total of 5 hours:

12:15 - 10:15 = 2 hours 
14:15 - 12:15 = 2 hours 
17:00 - 16:00 = 1 hours

The query result must be 5.

The idea was concatenate start hour/minute and finish hour/minute, convert them to hour, make the difference, calculating the total.

SELECT (Start_Hour & ":" & Start_Minute) as start, (Finish_Hour & ":" & Finish_Minute) as finish

FROM OrderDetails

But when I try to convert them to HH:MM using cast or convert but I got errors.

Any advice? Thank you

CodePudding user response：

This query uses make_time as Adrian Klaver suggests.

select 
  "Order", 
   sum(extract(hour from 
        make_time("Finish_Hour", "Finish_Minute", 0) - 
        make_time("Start_Hour",  "Start_Minute",  0))
      ) as duration
from the_table
group by "Order";

However I have remarks about your data design. Hour and minute are not enough for storing time because (apart from missing precision and other reasons) the end time might be over midnight. You have a specific data type for this - timestamp. I would suggest something like

create table the_table 
(
  order_nr integer, 
  start_time timestamp, 
  finish_time timestamp
);

Also note that using mixed case names in Postgresql requires double-quoting.

CodePudding user response：

Use make_time:

 select  make_time(12, 15, 0) - make_time(10, 15, 0);
 ?column? 
----------
 02:00:00

Where in your case you would substitute in Start_Hour, Start_Minute, Finish_Hour, Finish_Minute.