计算查询中的活动时间戳之间的时差 [英] Calculating time difference between activity timestamps in a query

问题描述

我非常新的访问和解决问题（我希望）一个简单的问题 - 想我可能正在通过Excel护目镜。

I'm reasonably new to Access and having trouble solving what should be (I hope) a simple problem - think I may be looking at it through Excel goggles.

我有一个名为 importedData 的表，我（不太奇怪）每天都会导入一个日志文件。该日志文件来自某些采矿设备上的简单数据记录应用程序，并且基本上它会保存 timestamp 和 status 对于当前活动更改为新活动的点。

I have a table named importedData into which I (not so surprisingly) import a log file each day. This log file is from a simple data-logging application on some mining equipment, and essentially it saves a timestamp and status for the point at which the current activity changes to a new activity.

数据示例如下所示：

然后使用查询定义范围我想查看的信息，例如从29/11/2013 06:00:00 AM到29/11/2013 06:00:00 PM

This information is then filtered using a query to define the range I want to see information for, say from 29/11/2013 06:00:00 AM until 29/11/2013 06:00:00 PM

现在的对象是采取一个状态条目的 timestamp 并获取它与记录之间的时差在后续行的查询结果中。由于设备工作时间为12小时，所以我应该可以建立一个设备花费多少时间来进行每一项活动的照片。

Now the object of this is to take a status entry's timestamp and get the time difference between it and the record on the subsequent row of the query results. As the equipment works for a 12hr shift, I should then be able to build a picture of how much time the equipment spent doing each activity during that shift.

例如，设备状态为START_SHIFT为00:01:00，状态DELAY_WAIT_PIT为06:08:26，并依此类推。然后，我将为所选择的期间建立一个唯一的状态条目列表，并将每个状态的总时间合计得到我的转移摘要。

In the above example, the equipment was in status "START_SHIFT" for 00:01:00, in status "DELAY_WAIT_PIT" for 06:08:26 and so-on. I would then build a unique list of the status entries for the period selected, and sum the total time for each status to get my shift summary.

推荐答案

您可以使用相关子查询来获取每行的下一个 timestamp 。

You can use a correlated subquery to fetch the next timestamp for each row.

SELECT
    i.status,
    i.timestamp,
    (
        SELECT Min([timestamp])
        FROM importedData
        WHERE [timestamp] > i.timestamp
    ) AS next_timestamp
FROM importedData AS i
WHERE i.timestamp BETWEEN #2013-11-29 06:00:00#
    AND #2013-11-29 18:00:00#;

然后，您可以将该查询用作另一个查询中的子查询，您可以在其中计算 timestamp 和 next_timestamp 。然后使用整个新查询作为您在 GROUP BY status 的第三个子查询，并计算每个状态的总持续时间。

Then you can use that query as a subquery in another query where you compute the duration between timestamp and next_timestamp. And then use that entire new query as a subquery in a third where you GROUP BY status and compute the total duration for each status.

这是我在Access 2007中测试的版本...

Here's my version which I tested in Access 2007 ...

SELECT
    sub2.status,
    Format(Sum(Nz(sub2.duration,0)), 'hh:nn:ss') AS SumOfduration
FROM
    (
        SELECT
            sub1.status,
            (sub1.next_timestamp - sub1.timestamp) AS duration
        FROM
            (
                SELECT
                    i.status,
                    i.timestamp,
                    (
                        SELECT Min([timestamp])
                        FROM importedData
                        WHERE [timestamp] > i.timestamp
                    ) AS next_timestamp
                FROM importedData AS i
                WHERE i.timestamp BETWEEN #2013-11-29 06:00:00#
                    AND #2013-11-29 18:00:00#
            ) AS sub1
    )  AS sub2
GROUP BY sub2.status;

如果遇到麻烦或需要修改它，请打破最内层的子查询， ，并自行测试。然后为 sub2 执行相同操作。我怀疑您将要更改 WHERE 子句以使用参数而不是硬编码的时间。

If you run into trouble or need to modify it, break out the innermost subquery, sub1, and test that by itself. Then do the same for sub2. I suspect you will want to change the WHERE clause to use parameters instead of hard-coded times.

请注意如果您的持续时间超过24小时，查询格式表达式将不适用。这是一个立即窗口会话，说明了问题...

Note the query Format expression would not be appropriate if your durations exceed 24 hours. Here is an Immediate window session which illustrates the problem ...

' duration greater than one day:
? #2013-11-30 02:00# - #2013-11-29 01:00#
 1.04166666667152 
' this Format() makes the 25 hr. duration appear as 1 hr.:
? Format(#2013-11-30 02:00# - #2013-11-29 01:00#, "hh:nn:ss")
01:00:00

但是，如果您仅处理12小时内的数据。轮班，这不应该是一个问题。请记住，如果您需要分析超过24小时的数据。

However, if you're dealing exclusively with data from 12 hr. shifts, this should not be a problem. Keep it in mind in case you ever need to analyze data which spans more than 24 hrs.

如果子查询不熟悉，请参阅Allen Browne的页面：子查询基础。他在题为获取另一条记录中的值的部分中讨论了相关的子查询。

If subqueries are unfamiliar, see Allen Browne's page: Subquery basics. He discusses correlated subqueries in the section titled Get the value in another record.

这篇关于计算查询中的活动时间戳之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！