SELECT AVG (closeTime - createTime)
FROM Deals
WHERE dealid = 123
'closeTime'和'addTime'具有DATETIME(YYYY-MM-DD HH:MI:SS)格式。结果是:
105030215.0000
我应该使用哪个函数将此值转换为DATETIME?
addTime: 12/04/2016 13:06
closeTime: 12/05/2017 16:08
我要查找的结果(我在Excel中计算出):
29:03:02:15 (DD:HH:MM:SS)
DATETIME
在MySQL中减s似乎不会在几秒钟内给它们带来差异。以您的两个示例日期为2016-04-12 13:06和2016-05-12 16:08(假设您的问题中的2017年是错字),减去它们将返回100030200,而正确的答案是2602920。系数是38.4一点点。
返回秒数差异的MySQL函数TIMESTAMPDIFF
实际上是,并且SELECT TIMESTAMPDIFF(SECOND, '2016-04-12 13:06', '2016-05-12 16:08')
返回正确的秒数。
现在,我们要做的就是将其转换为您的格式。MySQL具有一个内置的功能SEC_TO_TIME
,可以执行此操作,但是不幸的是,它不能在超过839小时的时间内正常工作,并且无法单独列出日期。我们可以通过以下方式来解决此问题:将日期与其余计算分开计算,并SEC_TO_TIME
在从差额中减去了天数后就使用剩余数。
SELECT CONCAT(
TIMESTAMPDIFF(DAY, '2016-04-12 13:06', '2016-05-12 16:08'), ':',
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2016-04-12 13:06', '2016-05-12 16:08') % 86400));
-- 30:03:02:00 (DD:HH:MM:SS)
这似乎是正确的答案。尽管您期望的输出是29:03:02:15
,但是四月份有30天,而输入中根本没有秒。
当您使用汇总函数确定秒数时,我们将无法选择以天为单位的值和以秒为单位的值(否则,我们将获得平均天数,然后是平均数的一部分)秒数),因此您必须将其重写为
SELECT CONCAT(
FLOOR(AVG(TIMESTAMPDIFF(SECOND, createTime, closeTime)) / 86400), ':',
SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, createTime, closeTime)) % 86400))
FROM deals WHERE dealid = 123;
-- 30:03:02:00.0000
现在这包括几分之一秒,因为平均差可能不是整数秒。要排除它们,请使用FLOOR
或ROUND
。
SELECT CONCAT(
FLOOR(AVG(TIMESTAMPDIFF(SECOND, createTime, closeTime)) / 86400), ':',
SEC_TO_TIME(ROUND(AVG(TIMESTAMPDIFF(SECOND, createTime, closeTime))) % 86400))
FROM deals WHERE dealid = 123;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句