时间戳

首页 > 解决方案 > 使用mysql计算每天的工作时间

mysql - 使用mysql计算每天的工作时间

问题描述

我想使用 MySQL 计算每天的工作时间。我展示了很多小时的计算解决方案，但它们都不符合我的要求。在我的表中，我的外场没有。我必须考虑第一次进入和第二次离开，并据此计算工作时间。

表结构：

CREATE TABLE IF NOT EXISTS `timesheet` (
  `MachineNo` int(6) unsigned NOT NULL,
  `Empcardno` int(3) unsigned NOT NULL,
  `Date` date  NOT NULL,
  `Time` time  NOT NULL
) DEFAULT CHARSET=utf8;

INSERT INTO `timesheet` 
(`MachineNo`, `Empcardno`, `Date`,`Time`) VALUES
    (01,    5,  '2020-05-22',   '18:15:54'),    
    (01,    5,  '2020-05-22',   '14:46:47'),
    (01,    5,  '2020-05-22',   '14:26:05'),
    (01,    5,  '2020-05-22',   '09:26:30'),
    (01,    5,  '2020-05-21',   '18:15:45'),
    (01,    5,  '2020-05-21',   '14:48:39'),
    (01,    5,  '2020-05-21',   '14:29:55'),
    (01,    5,  '2020-05-21',   '09:37:49');

MySql 小提琴链接

我尝试了以下查询，但它只给了我最大和最小时间之间的总小时数。它不考虑所有的输入输出值。我想考虑所有进出点之间的时间。

SELECT
  Empcardno,min(Time),
  max(Time),
  TIMEDIFF(max(Time),
  min(Time)) As Diff_Value
FROM
  timesheet
GROUP BY
  DATE(Date),Empcardno

结果

   Empcardno    Date       min(Time)    max(Time)   Diff_Value
    5         2020-05-21    09:37:49    18:15:45    08:37:56
    5         2020-05-22    09:26:30    18:15:54    08:49:24

但我想将时间计算为这两者之间的时间 

 (01,    5,  '2020-05-21',   '14:29:55'),
 (01,    5,  '2020-05-21',   '09:37:49');

和

(01,    5,  '2020-05-21',   '18:15:45'),
(01,    5,  '2020-05-21',   '14:48:39'),

标签： mysql

解决方案

这是一个在没有窗口函数的情况下要解决的棘手问题……基本上，您需要为每个日期的每台机器的每个条目生成行号。然后，您可以组合奇数行和偶数行来生成时间差，可以将其相加以生成当天的总时间。为此，我已将奇数行的时间转换为负数，以便将该值添加到下一个值（从偶数行），我们得到两者之间的差异。

SELECT MachineNo, Empcardno, Date, SEC_TO_TIME(SUM(tsecs)) AS total_time
FROM (
  SELECT CASE WHEN MachineNo = @mn AND Empcardno = @en AND `Date` = @dt
              THEN @rn := @rn + 1
              ELSE @rn := 1
         END AS rn,
         @mn := MachineNo AS MachineNo, @en := Empcardno AS Empcardno, 
         @dt := `Date` AS `Date`,
         CASE WHEN @rn % 2 = 1 THEN -TIME_TO_SEC(`Time`)
              ELSE TIME_TO_SEC(`Time`)
         END AS tsecs
  FROM timesheet
  CROSS JOIN (SELECT @mn := 0, @en := 0, @dt := '', @rn := 0) init
  ORDER BY Date, Time
) t
GROUP BY MachineNo, Empcardno, Date

输出：

MachineNo   Empcardno   Date        total_time
1           5           2020-05-21  08:19:12
1           5           2020-05-22  08:28:42

SQLFiddle 上的演示

推荐阅读