如何设计工作时间数据库表

Question

嗨，伙计们，我想帮助我的朋友设计数据库表。这是一个系统跟踪工人的工作时间在工厂通过读取卡片信息从某些读卡器。每次工作人员登录他的进出信息时，都会保存一条记录。

我的问题是，我如何计算每个工人的工作时间(分钟)，每个工作日?工人可以从8:00AM~20:00PM，或20:00PM~8:00AM工作。

有人能帮我吗?

谢谢!

---

你们确实给了我很多帮助。以前的设计是带有记录内或记录外的表。我很难确定哪些属于同一工作时间范围。现在我使用另一个表，其中的记录在同一记录中同时具有in-time和out-time。插入以节省在工时，更新以节省超时时间，这样可以轻松计算在工时和超时时间之间的总分钟数

Answer 1

SELECT datediff(hh,'2011-08-30 04:47','2011-08-30 05:48') as [Hour(s) Worked]
Hour(s) Worked
--------------
1

Answer 2

两个表的简单例子

[TblUsers]
User_id        PK
FirstName
LastName
[TblSchedule]
Schedule_id    PK
User_id        FK
Date_From
Date_To

要获得带有时间的每日工作网格，您可以这样写:

SELECT 
  u.FirstName + ' ' + u.LastName as [username],
  CAST(FLOOR(CAST(@datetime as float)) as datetime) as [date],
  DATEDIFF(minute, s.Date_To, s.Date_From) as [workMinutes]
FROM 
  [TblSchedule] s, [TblUsers] u
WHERE
 s.user_id = u.user_id
GROUP BY 
  u.FirstName + ' ' + u.LastName,
  CAST(FLOOR(CAST(@datetime as float)) as datetime)
ORDER BY 
 s.Date_From;

Answer 3

只需计算该worker的每个IN-Record与以下OUT-Record之间的分钟数。如果你想用一整天，那就去取相关记录，总结相关差异。

这里更复杂的事情是当一些工人忘记冲压。你的程序必须为这种情况做好准备。

还要注意夏令时之类的事情。时间计算可以非常复杂。

我想我会在应用程序级别进行计算，而不是在SQL中。

Answer 4

DATEDIFF可以给你一些奇怪的结果。例如，采取这两个DATETIME2(我假设你有SQL Server 2008)值有5分钟的差异:

SELECT DATEDIFF(hh,'2011-01-01 04:59:00','2011-01-01 05:04:00') 
Results
-----------
1

结果是莫名其妙的奇怪:1小时。奇怪，因为分钟的差值是5分钟而小时的差值是1小时我们知道1小时= 60分钟。请阅读这篇文章，看看解释。

解决方案:

1)用DATEDIFF(mi,...)代替DATEDIFF(hh,...)例:

SELECT DATEDIFF(mi,'2011-01-01 07:55:00','2011-01-01 16:02:00') [Minutes]
    ,DATEDIFF(mi,'2011-01-01 07:55:00','2011-01-01 16:02:00')/60 [Hours] 
    --8 hours
    ,DATEDIFF(mi,'2011-01-01 07:55:00','2011-01-01 16:02:00')%60 [Additional minute] 
    --7 minute

但:

SELECT DATEDIFF(mi,'2011-01-01 08:00:59','2011-01-01 16:00:05') [Minutes] 
        --480
        ,DATEDIFF(ss,'2011-01-01 08:00:59','2011-01-01 16:00:05')/60 [Seconds/60] 
        --479

2)不使用DATEDIFF函数(DATETIME[2][OFFSET]数据类型)，使用DATETIME值和-运算符:

DECLARE @Test TABLE
(
    TestId INT IDENTITY(1,1) PRIMARY KEY
    ,[Enter] DATETIME NOT NULL
    ,[Exit] DATETIME NOT NULL
);
INSERT  @Test
VALUES  ('2011-01-01 07:55:00','2011-01-01 16:02:02')
        ,('2011-01-01 08:00:59','2011-01-01 16:00:05');
SELECT  *
        ,t.[Exit] - t.[Enter] AS MyDateDiff
        ,DATEPART(hh,t.[Exit] - t.[Enter]) [Hours]
        ,DATEPART(mi,t.[Exit] - t.[Enter]) [Additional minutes]
        ,DATEPART(ss,t.[Exit] - t.[Enter]) [Additional seconds]
FROM    @Test t

结果:

TestId      Enter                   Exit                    MyDateDiff              Hours       Additional minute Additional seconds
----------- ----------------------- ----------------------- ----------------------- ----------- ----------------- ------------------
1           2011-01-01 07:55:00.000 2011-01-01 16:02:02.000 1900-01-01 08:07:02.000 8           7                 2
2           2011-01-01 08:00:59.000 2011-01-01 16:00:05.000 1900-01-01 07:59:06.000 7           59                6