mysql - 如何从 IF 子句返回时间

Question

我在从条件选择输出时间时遇到问题。如果时间 < 6 小时，目标是减去 30 分钟。

这是一个演示:

SELECT TIME(start_time) AS start,
    TIME(end_time) AS end,
    TIME_TO_SEC(TIMEDIFF(end_time, start_time)) AS durationSecs,
    IF(
        TIMEDIFF(end_time, start_time) >= "06:00:00",
        SUBTIME(TIMEDIFF(end_time, start_time), 30:0:0),
        TIMEDIFF(end_time, start_time)
    ) AS duration
FROM [...]

此代码返回我认为是 TIME 对象本身的字符串表示形式，例如 30303a30353a3030 。

如何让它输出实际时间？

Answer 1

SELECT TIME(start_time) AS start,
    TIME(end_time) AS end,
    TIME_TO_SEC(TIMEDIFF(end_time, start_time)) AS durationSecs,
    IF(
        TIMEDIFF(end_time, start_time) >= "06:00:00",
        TIMEDIFF(end_time - INTERVAL 30 MINUTE, start_time),
        TIMEDIFF(end_time, start_time)
    ) AS duration
FROM [...]