设置列值等于columnB减去columnA

Question

我希望我的MySql列'Time‘被更新为'End_Time’减去列'Start_Time‘

注: End_Time和Start_Time采用日期时间格式.

谢谢

Answer 1

试试这个方法，

SELECT SEC_TO_TIME(TIMESTAMPDIFF(second,Start_Time,End_Time)) --include sec
or
SELECT SEC_TO_TIME(TIMESTAMPDIFF(minute,Start_Time,End_Time)*60) --diff in minute

eg

SELECT SEC_TO_TIME(TIMESTAMPDIFF(minute,'2016-05-04 10:00:00','2016-05-04 11:29:00')*60)

在这里，TIMESTAMPDIFF(minute将在几分钟内返回差异，并将它*60乘以秒，并使用SEC_TO_TIME进行秒到秒的转换。

Answer 2

在mysql中使用Datediff函数

请看这里的DATEDIFF

Answer 3

您可以使用TIMESTAMPDIFF函数。

SELECT TIMESTAMPDIFF(minute, '2015-06-01 13:13:55', '2015-06-03 10:15:20')

SELECT TIMESTAMPDIFF(second, '2015-06-01 13:13:55', '2015-06-03 10:15:20')

SELECT TIMESTAMPDIFF(hour, '2015-06-01 13:13:55', '2015-06-03 10:15:20')