select st.card_id, st.dept_id ,st.staff_id, w.real_work_time, w.start_work_time, w.end_work_time,
(TIME_TO_SEC(w.end_work_time) - TIME_TO_SEC(w.start_work_time))/3600 calc_work_time
from dat_staff_extend st
left join dat_dept dp on dp.dept_id=st.dept_id
left join his_worktime_detail w on w.staff_id=st.staff_id
where dp.name='掘一队2组'
and w.start_work_time<'2018-11-01 00:00:01' and w.start_work_time > '2018-10-31 00:00:00'
and w.real_work_time>0
SEC_TO_TIME(120)
但是TIME_TO_SEC 是把 时分秒转为 秒,并不会对年月日进行处理;
如果业务有需要可以使用UNIX_TIMESTAMP
UNIX_TIMESTAMP 函数返回的是自1970年以来的秒数,所以时间计算业务需要的话可以使用这个函数
应用场景计算两个时间差开始时间 结束时间
SELECT (UNIX_TIMESTAMP(STR_TO_DATE(end_time,'%Y-%m-%d %H:%i:%s')) -
UNIX_TIMESTAMP(STR_TO_DATE(begin_time,'%Y-%m-%d %H:%i:%s'))) useTime
SELECT (UNIX_TIMESTAMP(STR_TO_DATE('2017-10-1 00:05:00','%Y-%m-%d %H:%i:%s')) -
UNIX_TIMESTAMP(STR_TO_DATE('2017-10-1 00:00:00','%Y-%m-%d %H:%i:%s'))) useTime