Question 21824 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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 ;
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search