mysql求两字段相减_Mysql两个⽇期字段相减得到秒
前⾔：测试⼯程师⽇常，为了验证字段：平均回复时长 显⽰是否正确，所以需要将2个⽇期字段进⾏相减，得出的数据转化为⼩时，然后看⼩时落在哪个区间端⾥边，前端就显⽰哪个区间端。规则：<=5分钟，显⽰5分钟内；<=30分钟，显⽰30分钟内；<=1⼩时，显⽰1⼩时内，依次类推，直到>=24⼩时，显⽰未知。
医生座右铭
⼀、初步计算
测试数据如下，需求是：将字段dtm_accept 和字段dtm_crt 两列进⾏相减，得出的数据累加并除以条数，根据返回的结果，取对应的区间。公章遗失补办流程
SQL语句：神农架大九湖
lect dtm_accept , dtm_crt,
TIMESTAMPDIFF(SECOND,dtm_accept,dtm_crt) as aSecond,
TIMESTAMPDIFF(MINUTE,dtm_accept,dtm_crt) as bMinute,
TIMESTAMPDIFF(HOUR,dtm_accept,dtm_crt) as cHour
笑着哭三线表怎么做from test
执⾏上⾯的sql语句，得出以下：
⼿动将aSecond这⼀列，相加 并换算为⼩时数，最后的结果=⼩时数/6(条数)
179740 秒=49.9277778 时
49.93/6=8.32 所以显⽰的区间数应该是：9⼩时内九十九猜一字
⼆、进⼀步优化：
lect
dtm_accept ,
dtm_crt,
TIMESTAMPDIFF(SECOND,dtm_accept,dtm_crt) as aSecond, TIMESTAMPDIFF(MINUTE,dtm_accept,dtm_crt) as bMinute, TIMESTAMPDIFF(HOUR,dtm_accept,dtm_crt) as cHour
from test
union ALL
lect
除腋下臭味' ' ,
'合计',
SUM(TIMESTAMPDIFF(SECOND,dtm_accept,dtm_crt)) ,
SUM(TIMESTAMPDIFF(MINUTE,dtm_accept,dtm_crt)) ,
SUM(TIMESTAMPDIFF(HOUR,dtm_accept,dtm_crt) )
from test
通过sql语句，直接计算出毫秒数，转成⼩时在除以6 ，
49.93/6=8.32 所以显⽰的区间数应该是：9⼩时内
三、扩展：将带毫秒的时间格式化为不带毫秒的时间h0t
lect dtm_upt,DATE_FORMAT(dtm_upt,"%Y-%m-%d %H:%i:%S") from test
