同一个表中两个记录之间的日期差异

I have a table (job_logs) with the following records: id, job_id, user_id, status, created_at, job_type.

我有一个表（job_logs），其中包含以下记录：id，job_id，user_id，status，created_at，job_type。

Each time a job starts to run a record is written in the job_log table with status='started'. When a job finish running another record is added to the table with status='completed'.

每次作业开始运行时，记录都会写入job_log表，status ='started'。当一个作业完成运行时，另一个记录被添加到status ='completed'的表中。

Both records has the same user_id, job_type and job_id (which is determined by the process running the job - unique to these 2 records).

两个记录具有相同的user_id，job_type和job_id（由运行作业的进程确定 - 这两个记录是唯一的）。

I want a query that will return all these records pairs in the table (ordered by id desc) but the tricky part is that I want to add to the record with the 'completed' status the time it took the job to run (completed.created_at - started.created_at).

我想要一个将返回表中所有这些记录对的查询（由id desc排序），但棘手的部分是我想要将具有“已完成”状态的记录添加到作业运行所花费的时间（已完成。 created_at - started.created_at）。

How can I do that?

我怎样才能做到这一点？

1 个解决方案

#1

SELECT j1.job_id AS job_id, (j2.created_at - j1.created_at) AS time_run
FROM job_logs j1 INNER JOIN job_logs j2 ON (j1.job_id = j2.job_id)
WHERE j1.status = 'started' AND j2.status = 'completed'