MySQL automatically calculate the hour difference between two TIME values with decimal places

Question

My table has the following columns

Column	Data type
(PK) table_id	INT AUTO_INCREMENT
start_time	TIME
end_time	TIME
duration	DECIMAL(10,2)

When someone adds a row to this table, I want it to calculate the difference in hours between the start and end time, and save the result to the duration column, For example:

INSERT INTO my_table(start_time, end_time) 
VALUES ("9:00:00","17:30:00")
-- Duration = 8.5

INSERT INTO my_table(start_time, end_time) 
VALUES ("20:00:00","4:00:00")
-- Duration = 8

I want the time to be independent of the date so these rows can be used for different days using the PK.

I have created a procedure to automatically calculate the duration using TIMEDIFF and HOUR to cast the time to a number, but it only returns whole numbers and the values are not always accurate.

For example, using "20:30:00" and "4:00:00" (start 8:30pm and go to 4:00am), returns 16, when it should return 7.5.

DELIMITER //
CREATE PROCEDURE add_row(
_start_time  TIME,
_end_time TIME
)
BEGIN
DECLARE _duration DECIMAL(10,2);
SELECT HOUR(TIMEDIFF(_start_time,_end_time)) INTO _duration;

INSERT INTO my_table(start_time,end_time,duration) VALUES
(_start_time,_end_time,_duration);
END; //

DELIMITER ;

Answer 1

Simplest way is calculate like this

(TIME_TO_SEC(_end_time) - TIME_TO_SEC(_start_time))

Will give you seconds, after that divide by second in hour, use TRUNCATE

insert into my_table(start_time,end_time, duration) select _start_time,_end_time, 
TRUNCATE(
  (TIME_TO_SEC(_end_time) - TIME_TO_SEC(_start_time))/3600
   , 2);

No need function here, but you can add if you wish.

Format number to 2 decimal places

https://www.w3schools.com/sql/sql_insert_into_select.asp

https://www.w3resource.com/mysql/date-and-time-functions/mysql-time_to_sec-function.php

If you need it also work with "next day" do something like this

 (TIME_TO_SEC(_end_time) - TIME_TO_SEC(_start_time)) + 
   case when _start_time > _end_time then 24*3600 else 0 end 

Which mean add 24hr if value is next day.