MySQL自动计算两个TIME值的带小数小时差问题

你的表结构如下：

需求是插入数据时自动计算start_time与end_time的小时差（保留小数），且不考虑日期（跨天场景需正确计算，比如20:30到次日4:00应得到7.5小时）。原存储过程使用HOUR(TIMEDIFF(...))仅能返回整数小时，且跨天计算结果错误，以下是两种修正方案：

方案1：修正存储过程 #

核心逻辑是先计算两个时间的总秒数差，再转换为小时数，同时处理跨天场景（当end_time早于start_time时，视为跨天，追加24小时对应的秒数）。

修正后的存储过程代码：

DELIMITER //
CREATE PROCEDURE add_row(
    _start_time TIME,
    _end_time TIME
)
BEGIN
    DECLARE _duration DECIMAL(10,2);
    -- 计算总秒数差，处理跨天情况后转换为小时并保留两位小数
    SELECT 
        ROUND(
            IF(
                _end_time >= _start_time,
                TIMESTAMPDIFF(SECOND, _start_time, _end_time),
                TIMESTAMPDIFF(SECOND, _start_time, _end_time) + 86400
            ) / 3600,
            2
        ) INTO _duration;

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

逻辑说明 #

• 使用TIMESTAMPDIFF(SECOND, ...)获取精准的秒数差，比TIMEDIFF更适配跨天计算场景
• 当end_time早于start_time时，追加86400秒（24小时的总秒数），模拟跨天的时间差
• 总秒数除以3600转换为小时，通过ROUND(...,2)保留两位小数

测试验证 #

• 插入("9:00:00","17:30:00")，计算得8.5小时
• 插入("20:30:00","4:00:00")，计算得7.5小时
• 插入("20:00:00","4:00:00")，计算得8小时

方案2：使用触发器（更自动化） #

若希望直接执行INSERT语句即可自动计算时长，无需调用存储过程，可使用BEFORE INSERT触发器：

DELIMITER //
CREATE TRIGGER calculate_duration_before_insert
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    SET NEW.duration = ROUND(
        IF(
            NEW.end_time >= NEW.start_time,
            TIMESTAMPDIFF(SECOND, NEW.start_time, NEW.end_time),
            TIMESTAMPDIFF(SECOND, NEW.start_time, NEW.end_time) + 86400
        ) / 3600,
        2
    );
END; //
DELIMITER ;

使用方式 #

直接执行插入语句，触发器会自动填充duration列：

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:30:00","4:00:00"); -- duration自动设为7.5

内容的提问来源于stack exchange，提问作者Singhmansingh