我想要显示的格式为相差X天X小时X分钟X秒
利用TIMESTAMPDIFF()计算两个时间相差的时间
SELECT CONCAT(FLOOR(TIMESTAMPDIFF(SECOND, '-08-12 00:14:30', '-09-04 08:44:53') / 86400), '天',TIMESTAMPDIFF(HOUR, '-08-12 00:14:30', '-09-04 08:44:53') % 24, '小时',TIMESTAMPDIFF(MINUTE, '-08-12 00:14:30', '-09-04 08:44:53') % 60, '分',TIMESTAMPDIFF(SECOND, '-08-12 00:14:30', '-09-04 08:44:53') % 60, '秒') SPENDTIME
结果：
23天8小时30分23秒
但是如果你知道了相差了多少秒，那么可以利用NOW()和DATE_ADD()来进行计算
SELECTCONCAT(FLOOR(TIMESTAMPDIFF(SECOND, NOW(), DATE_ADD(NOW(), INTERVAL SPENDTIME SECOND)) / 86400), '天',TIMESTAMPDIFF(HOUR, NOW(), DATE_ADD(NOW(), INTERVAL SPENDTIME SECOND)) % 24, '小时',TIMESTAMPDIFF(MINUTE, NOW(), DATE_ADD(NOW(), INTERVAL SPENDTIME SECOND)) % 60, '分',TIMESTAMPDIFF(SECOND, NOW(), DATE_ADD(NOW(), INTERVAL SPENDTIME SECOND)) % 60, '秒')FROM (SELECTAVG(UNIX_TIMESTAMP(COMPLETE_TIME) - UNIX_TIMESTAMP(IMPLEMENT_TIME)) SPENDTIMEFROM WKORDER_TASK_TASKINFO) a
