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 ;

arheops · Accepted Answer · 2022-12-10T22:46:13.513

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.

score 1 · Answer 2 · answered Dec 10 '22 at 05:55

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.

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

2 Answers2