计算两个时间记录之间的差异 [英] Calculate difference between two time records

问题描述

我发现了许多与我的问题相似但又不完全相同的问题，我在其中发现的任何问题都没有帮助我，所以这里是新问题.

I found many questions similar to mine but not quite the same, and nothing I found in them helped me so here is new one.

预期输出:如果用户相同，则一次记录与前一次记录之间的差异.

Expected output: Difference between one time record and previous one if the User is the same.

示例表:

+-------+--------+-------------------+------+   
| RowID | User   | Godzina_transakcji| Diff |   
+-------+--------+-------------------+------+   
|    1  | AAA    | 14:23:03          |      |   
|    2  | AAA    | 14:23:57          |      |   
|    3  | AAA    | 14:25:03          |      |   
|    4  | BBB    | 03:37:23          |      |   
|    5  | BBB    | 03:39:21          |      |   
|    6  | BBB    | 05:23:11          |      |   
+-------+---------+------------------+------+ 

所以查询应该为第 1 行给出 0，为 2 给出 =14:23:57-14:23:03 等等.

So query should for row 1 give 0, for for 2 give =14:23:57-14:23:03 and so on.

到目前为止我有这样的东西(基于@Tom Collins 在其他问题中的回答)但由于某种原因它给出了 [Error]

So far I have something like this (based on @Tom Collins answer in other question) but for some reason it gives [Error]

 SELECT tblTemp2.RowID,
       tblTemp2.User,
       tblTemp2.Godzina_transakcji,
       Nz(Dmax("Godzina_transakcji", "User", "(Godzina_transakcji <" & [Godzina_transakcji] &
                                             ") and (User = '" & [User]
                                                        &"')"), 0) - [Godzina_transakcji]  AS Diff
FROM   tblTemp2; 

我在这方面做错了什么?另外，在另一个步骤中，我将不得不考虑中间经过午夜.

What am I doing wrong in this one? Also, on another step I will have to take account of passing midnight in between.

结论:
如果有人会寻找类似的问题，这里是对我有用的代码.谢谢古斯塔夫，你是神！

Conclusion:
If anyone will look for similar problem here is code that worked for me. Thanks Gustav, you are a God!

SELECT 
    tblTemp2.RowID,
    tblTemp2.User,
    tblTemp2.Godzina_transakcji,
    Format((    Select TimeValue(T.Godzina_transakcji) 
                From tblTemp2 As T
    Where tblTemp2.RowID = T.RowID + 1  And tblTemp2.User = T.User ) - TimeValue([Godzina_transakcji]), "hh:nn:ss") As Diff
FROM
   tblTemp2;

推荐答案

那是因为你次存储为文本.您必须转换为真实时间才能直接比较:

That is because you times stored as text. You must convert to true time to compare directly:

SELECT tblTemp2.RowID,
       tblTemp2.User,
       tblTemp2.Godzina_transakcji,
       Nz(Dmax("Godzina_transakcji", "tblTemp2", "(TimeValue([Godzina_transakcji]) < #" & [Godzina_transakcji] & "#) and (User = '" & [User] & "')"), #00:00#) - TimeValue([Godzina_transakcji])  AS Diff
FROM   tblTemp2; 

使用 ID:

SELECT 
    tblTemp2.RowID,
    tblTemp2.User,
    tblTemp2.Godzina_transakcji,
    (Select TimeValue(T.Godzina_transakcji) 
    From tblTemp2 As T
    Where T.RowID = tblTemp2.RowID + 1 And T.User = tblTemp2.User) - TimeValue([Godzina_transakcji]) As Diff
FROM
   tblTemp2;

这篇关于计算两个时间记录之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！