MySQL如何减去两个DATETIME和INT值

Question

我将“start_time”、“finish_time”和“meal”时间存储为

start_time = DATETIME
finish_time = DATETIME
meal = INT - store minitues

现在我想获得 start_time 和 finish_time 之间的差异，之后我想从小时数中减去用餐时间如何做到这一点，

eg:
start_time             finish_time           meal
2014-10-24 08:30:00    2014-10-24 18:30:00   45

输出应为:9:15

如何仅使用 MySQL 来获取它，

我尝试使用 TIMESTAMPDIFF 但我不知道如何减去膳食并将其转换为 H:m 格式:

TIMESTAMPDIFF(MINUTE,start_time, finish_time)

Answer 1

只需进行普通减法即可:

TIMESTAMPDIFF(MINUTE,start_time, finish_time) - meal

要将其转换为 H:m 格式，请将其除以 60 并从 60 中获取模数:

CONCAT(FLOOR((TIMESTAMPDIFF(MINUTE,start_time, finish_time) - meal)/60),
      ':', 
      (TIMESTAMPDIFF(MINUTE,start_time, finish_time) - meal) % 60)