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