Mysql 2条记录 差值计算

1 表结构

 

2:  其实 是2个相同的 表根据rownum= rownum-1 来计算，所以先了解单个表的查询

 

附上SQL:

#查询出1天的数据升序
select (@rownum := @rownum + 1) as rownum,  info.equipment_id,  info.upload_time
from t_box_status_info info, (select @rownum := 0) r
where info.upload_time >=  STR_TO_DATE('2017-07-24 00:00:00','%Y-%m-%d %H:%i:%s') 
  and info.upload_time <=  STR_TO_DATE('2017-07-24 23:59:59','%Y-%m-%d %H:%i:%s')
order by info.upload_time

 

3:2个相同的表计算差值秒

 

select t1.equipment_id, t1.rownum t1_rownum, t2.rownum t2_rownum,
t1.upload_time t1_upload_time, 
t2.upload_time t2_upload_time,
(UNIX_TIMESTAMP(t1.upload_time) - UNIX_TIMESTAMP(t2.upload_time)) diff_second
from
( select (@rownum := @rownum + 1) as rownum,  info.equipment_id,  info.upload_time
  from t_box_status_info info, (select @rownum := 0) r
  where info.upload_time >=  STR_TO_DATE('2017-07-24 00:00:00','%Y-%m-%d %H:%i:%s') 
    and info.upload_time <=  STR_TO_DATE('2017-07-24 23:59:59','%Y-%m-%d %H:%i:%s')
  order by info.upload_time
) t1 
left join 
( select (@index := @index + 1) as rownum,  info.equipment_id,  info.upload_time
  from t_box_status_info info, (select @index := 0) r
  where info.upload_time >=  STR_TO_DATE('2017-07-24 00:00:00','%Y-%m-%d %H:%i:%s') 
    and info.upload_time <=  STR_TO_DATE('2017-07-24 23:59:59','%Y-%m-%d %H:%i:%s')
  order by info.upload_time
) t2
on 
t1.equipment_id = t2.equipment_id and t1.rownum = t2.rownum - 1

 

4:秒的差值计算

 　(UNIX_TIMESTAMP(time1) - UNIX_TIMESTAMP(time2))    diff_second

   分钟差值计算

     (UNIX_TIMESTAMP(endDateTime) - UNIX_TIMESTAMP(beginDateTime))  /  60 

   天数差值计算

     (UNIX_TIMESTAMP(endDateTime) - UNIX_TIMESTAMP(beginDateTime)) / (60 * 60 * 24) 

  这里可以自己算叻