MYSQL中的计时和超时查询

Question

如何查询mysql，两个不同日期的my (time in)和(out)的时长。例如，星期六06-06-2020晚上10:00上班，周日07-06-2020早上6:00我的休息时间…，晚上值班？

数据库：

​

示例结果：

---------------------------------------
|Date| HOURS    | DUTY  | TIME        |
|               |       |             |
|6-7 | 8 hours  | NIGHT | 10pm - 6am  |
|7-8 | 8 hours  | NIGHT | 10pm - 6am  |
|8-9 | 8 hours  | NIGHT | 10pm - 6am  |
|10  | 8 hours  | DAY   | 8am - 5pm   |
|11  | 8 hours  | DAY   | 8am - 5pm   |
---------------------------------------

Answer 1

设置@row_number1 = 0；设置@row_number2 = 0；

SELECT (IF(DATE_FORMAT(timein，'%d') = DATE_FORMAT(timeout，'%d')，DATE_FORMAT(timein，'%d')，CONCAT_WS('-'，DATE_FORMAT(timein，'%d')，DATE_FORMAT(timeout，‘%d’) as day，timein，timeout，(TIMEDIFF(timeout，timein)) as time_duration FROM

(选择tbl1.timein、tbl2.timeout

(SELECT (@row_number1:=@row_number1 + 1) as num，timein FROM( SELECT (case when DwInOutMode =0 then DATE_FORMAT(DateTimeRecord，‘%Y-%m-%d%H：%I：%s’) else Null end) as timein from biologs WHERE IndRegID =3 AND DATE_FORMAT(DateOnlyRecord，'%m/%Y') = '06/2020') AS tbletimein timein where timein不是NULL) as tbl1

加入

(SELECT (@row_number2:=@row_number2 + 1) as num，timeout FROM( SELECT (case when DwInOutMode =1 then DATE_FORMAT(DateTimeRecord，‘%Y-%m-%d%H：%i：%s’) else Null end) as timeout from biologs WHERE IndRegID =3 AND DATE_FORMAT(DateOnlyRecord，'%m/%Y') = '06/2020') AS tbletimein where timeout IS NOT NULL) as tbl2

在tbl1.num =tbl2.num上)作为tbl_payroll

Answer 2

为了灵活起见，我假设您在laravel中使用datatable，但最好的方法是在php数组循环中加载数据，然后在mysql中再次获取数据。祝你好运！