能够使用SQL查询计算表中两个不同行之间的时间差

Question

我正在尝试找到一种方法来确定表中两个单独行之间的时间量。

例如，我的表有user、date和time stamp、事务类型

数据示例：

Joe    10/2/2020 9:01:30     Shipping
Joe    10/2/2020 9:01:55     Shipping
Joe   10/02/2020 9:05:30     Shipping

我希望在我的输出中看到

user      date/time               time difference
 Joe    10/2/2020 9:01:30
Joe    10/2/2020 9:01:55          25 seconds
Joe   10/02/2020 9:05:30        3 min 35 seconds

最终目标是拥有不同的事务类型，并找出用户在每种事务类型下工作了多长时间，我只有endtime可以使用。

Answer 1

适用于MYSQL服务器的版本8+

如果您使用的是最新版本的MySQL服务器，如8.0版，您可以尝试使用LAG()函数。

示例表：

CREATE TABLE WorkLog (
    employee_name varchar(10),
    Shipping_Time DATETIME,
    PRIMARY KEY (employee_name, Shipping_Time)
);

示例数据：

INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Joe","2020-10-02 12:00:00.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Joe","2020-10-02 12:01:30.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Joe","2020-10-02 12:05:30.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Joe","2020-10-02 12:10:00.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Joe","2020-10-02 12:20:00.000");

INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Sue","2020-10-02 12:00:00.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Sue","2020-10-02 12:01:30.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Sue","2020-10-02 12:05:30.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Sue","2020-10-02 12:10:00.000");
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES("Sue","2020-10-02 12:20:00.000");

SQL查询-获取以秒为单位的结果：

SELECT employee_name,
Shipping_Time,
TIMESTAMPDIFF(SECOND, (LAG(Shipping_Time,1) OVER (PARTITION BY employee_name)),Shipping_Time) AS TimeDiffInSeconds
FROM WorkLog
ORDER BY employee_name, Shipping_Time; 

输出：

| employee_name | Shipping_Time       | TimeDiffInSeconds |
| ------------- | ------------------- | ----------------- |
| Joe           | 2020-10-02 12:00:00 |                   |
| Joe           | 2020-10-02 12:01:30 | 90                |
| Joe           | 2020-10-02 12:05:30 | 240               |
| Joe           | 2020-10-02 12:10:00 | 270               |
| Joe           | 2020-10-02 12:20:00 | 600               |
| Sue           | 2020-10-02 12:00:00 |                   |
| Sue           | 2020-10-02 12:01:30 | 90                |
| Sue           | 2020-10-02 12:05:30 | 240               |
| Sue           | 2020-10-02 12:10:00 | 270               |
| Sue           | 2020-10-02 12:20:00 | 600               |

SQL查询-获取时间形式的结果(hh:mm:ss):

SELECT employee_name,
Shipping_Time,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, (LAG(Shipping_Time,1) OVER (PARTITION BY employee_name)),Shipping_Time)) AS "TimeDiff hh:mm:ss"
FROM WorkLog
ORDER BY employee_name, Shipping_Time;

输出：

| employee_name | Shipping_Time       | TimeDiff hh:mm:ss |
| ------------- | ------------------- | ----------------- |
| Joe           | 2020-10-02 12:00:00 |                   |
| Joe           | 2020-10-02 12:01:30 | 00:01:30          |
| Joe           | 2020-10-02 12:05:30 | 00:04:00          |
| Joe           | 2020-10-02 12:10:00 | 00:04:30          |
| Joe           | 2020-10-02 12:20:00 | 00:10:00          |
| Sue           | 2020-10-02 12:00:00 |                   |
| Sue           | 2020-10-02 12:01:30 | 00:01:30          |
| Sue           | 2020-10-02 12:05:30 | 00:04:00          |
| Sue           | 2020-10-02 12:10:00 | 00:04:30          |
| Sue           | 2020-10-02 12:20:00 | 00:10:00          |

参考：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag

用于MS SQL Server的

使用OP评论中的新信息：

示例表：

CREATE TABLE JSSHIPMENTTABLE (
    CREATEDBY varchar(10),
    CREATEDDATETIME DATETIME,
    PRIMARY KEY (CREATEDBY, CREATEDDATETIME)
);

示例数据：

INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Joe','2020-10-02 12:00:00.000');
INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Joe','2020-10-02 12:01:30.000');

INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Sue','2020-10-02 12:00:00.000');
INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Sue','2020-10-02 12:01:30.000');

INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Sam Colton','2020-10-01 12:00:00.000');
INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Sam Colton','2020-10-01 12:01:30.000');
INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Sam Colton','2020-10-01 12:05:30.000');
INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Sam Colton','2020-10-01 12:10:00.000');
INSERT INTO JSSHIPMENTTABLE(CREATEDBY, CREATEDDATETIME) VALUES('Sam Colton','2020-10-01 12:20:00.000');

MS SQL Server查询-以秒为单位获取结果：

SELECT CREATEDBY,
CREATEDDATETIME,
datediff(second,(lag(CREATEDDATETIME,1) over (PARTITION by CREATEDBY ORDER BY CREATEDDATETIME)),CREATEDDATETIME) as TimeDiffinSeconds
from JSSHIPMENTTABLE
where convert(date,CREATEDDATETIME,23) = '2020-10-01' and CREATEDBY like '%Colton%' order by CREATEDBY,CREATEDDATETIME

输出：

CREATEDBY   CREATEDDATETIME              TimeDiffinSeconds
----------------------------------------------------------
Sam Colton  2020-10-01 12:00:00.000      NULL
Sam Colton  2020-10-01 12:01:30.000      90
Sam Colton  2020-10-01 12:05:30.000      240
Sam Colton  2020-10-01 12:10:00.000      270
Sam Colton  2020-10-01 12:20:00.000      600

Answer 2

试试这个查询。您可以编辑DATEDIFF函数，为您提供分钟、小时、天或您想要的任何增量的差异。

SELECT a.user, a.[date/time], DATEDIFF(second, b.[date/time], a.[date/time]) AS [time difference]
FROM #YourTable a
OUTER APPLY (
    SELECT TOP 1 *
    FROM #YourTable b
    WHERE a.user = b.user
    AND b.[date/time] < a.[date/time]
    ORDER BY [date/time] DESC
) b
ORDER BY a.user, a.[date/time] ASC

Answer 3

-- Sample Table:
CREATE TABLE WorkLog (
    employee_name varchar(10),
    Shipping_Time DATETIME,
    PRIMARY KEY (employee_name, Shipping_Time)
);

-- Sample Data:
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES('Joe','2020-10-02 12:00:00.000');
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES('Joe','2020-10-02 12:01:30.000');
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES('Joe','2020-10-02 12:05:30.000');
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES('Joe','2020-10-02 12:10:00.000');
INSERT INTO WorkLog(employee_name, Shipping_Time) VALUES('Joe','2020-10-02 12:20:00.000');

Select *, 
FORMAT(Shipping_Time - LAG(Shipping_Time) OVER (ORDER BY employee_name), 'mm:ss') As timeDiference
From WorkLog
Order By Shipping_Time ASC

-- Results:
employee_name   Shipping_Time   timeDiference
Joe 2020-10-02  12:00:00.000    NULL
Joe 2020-10-02  12:01:30.000    01:30
Joe 2020-10-02  12:05:30.000    04:00
Joe 2020-10-02  12:10:00.000    04:30
Joe 2020-10-02  12:20:00.000    10:00