ผมชอบที่จะใช้ type datetime รวมวันที่กับเวลา อยู่ในฟีลด์เดียวกัน
stamp_check type datetime
Code (SQL)
select
tb1.em_id, tb1.stamp_check st, tb2.stamp_check en,
((@st:=unix_timestamp(tb2.stamp_check)) - (@en:=unix_timestamp(tb1.stamp_check))) timework,
(@late:=(@st - (@fixin:=unix_timestamp(concat(curdate(),' 08:00'))))) as latein,
((@fixout:=unix_timestamp(concat(curdate(),' 17:00')))) as beforeout,
(@en - @fixout - if(@late>0, @late, 0)) as overtime
from table as tb1, table as tb2
where tb1.em_id=tb2.em_id
and date(tb1.stamp_check) = date(tb2.stamp_check)
and tb1.action='in' and tb2.action='out'
and date(tb1.stamp_check)=curdate()