MySQL-计算多个行的时差 [英] MySql - calculate time difference for multiple rows

问题描述

我有一张表，上面有制造装配数据，包括时间戳.我正在尝试确定工作"开始之间的平均间隔(以分钟为单位).

I have a table with manufacturing assembly data, including timestamps. I'm trying to determine the average interval in minutes between 'job' starts.

我返回ID和时间的查询如下:

My query that returns the id and time looks like:

select job_id, job_started from JobTable where job_started >= '2016-07-01' and job_started <= '2016-07-31';

我正在寻找输出，该输出将是每行之间的时间差:

I'm looking for output that would be the difference in time between each row:

15 18岁 21岁 14 13

15 18 21 14 13

推荐答案

获取平均间隔(以秒为单位):

Get average interval in seconds:

select (to_seconds(max(job_started)) - to_seconds(min(job_started))) / (count(*) - 1) as average_interval_seconds
from JobTable
where date(job_started) >= '2016-07-01'
  and date(job_started) <= '2016-07-31'
;

获取所有间隔(以秒为单位):

Get all intervals in seconds:

select to_seconds((
  select t2.job_started
  from JobTable t2
  where t2.job_started > t1.job_started
    and date(t2.job_started) <= '2016-07-31'
  limit 1
)) - to_seconds(t1.job_started) as interval_seconds
from JobTable t1
where date(t1.job_started) >= '2016-07-01'
  and date(t1.job_started) <= '2016-07-31'
  and t1.job_started <> (
    select job_started
    from JobTable
    where date(job_started) <= '2016-07-31'
    order by job_started desc
    limit 1
  )
;

http://sqlfiddle.com/#!9/1f8dc3/2

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