My table has the following columns

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 ;

CodePudding user response：

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

CodePudding user response：

For the 2nd row to represent 8:30pm to 4:00am the next day, you need (20:30:00, 28:00:00).

select table_id,
       start_time,
       end_time,
       timediff(end_time, start_time).        as diff,
       hour(timediff(end_time, start_time))   as diff_hh,
       minute(timediff(end_time, start_time)) as diff_mm,
       second(timediff(end_time, start_time)) as diff_ss,
       hour(timediff(end_time, start_time))  
       minute(timediff(end_time, start_time)) / 60.0  
       second(timediff(end_time, start_time))  / 3600.0 as duration
  from ss;

Some examples to explain the idea of TIME data type:

table_id|start_time|end_time|diff     |diff_hh|diff_mm|diff_ss|duration|
-------- ---------- -------- --------- ------- ------- ------- -------- 
       1|  09:00:00|17:30:00| 08:30:00|      8|     30|      0|  8.5000|
       2|  20:30:00|04:00:00|-16:30:00|     16|     30|      0| 16.5000|
       3|  20:30:00|28:00:00| 07:30:00|      7|     30|      0|  7.5000| 

Please note that hour extract the hour part but ignore that sign.