2010-2-23 8:45:56 1
2010-2-23 8:50:52 1
2010-2-23 8:56:19 2
2010-2-23 8:59:34 1
2010-2-23 9:42:11 2 怎么计算 状态2(2010-2-23 8:56:19)与第一个状态1(2010-2-23 8:45:56)时间差,我只去部分数据!
2010-2-23 8:50:52 1
2010-2-23 8:56:19 2
2010-2-23 8:59:34 1
2010-2-23 9:42:11 2 怎么计算 状态2(2010-2-23 8:56:19)与第一个状态1(2010-2-23 8:45:56)时间差,我只去部分数据!
select floor((cast(to_timestamp('2010-2-23 8:50:52','yyyy-MM-dd hh24:mi:ss') as date) - cast(to_timestamp('2010-2-23 8:56:12','yyyy-MM-dd hh24:mi:ss') as date))*24*60) from dual;
是这样吧
times status
2010-2-23 8:45:56 1
2010-2-23 8:50:52 1
2010-2-23 8:56:19 2
2010-2-23 8:59:34 1
2010-2-23 9:42:11 2 是2010-2-23 8:56:19-2010-2-23 8:45:56=值1
2010-2-23 9:42:11-2010-2-23 8:59:34=值2 ,这个表记录的格式是这样的!
select to_date('2010-2-23 8:45:56','yyyy-mm-dd hh24:mi:ss') insettime, 1 status from dual union
select to_date('2010-2-23 8:50:52','yyyy-mm-dd hh24:mi:ss') insettime, 1 status from dual union
select to_date('2010-2-23 8:56:19','yyyy-mm-dd hh24:mi:ss') insettime, 2 status from dual union
select to_date('2010-2-23 8:59:34','yyyy-mm-dd hh24:mi:ss') insettime, 1 status from dual union
select to_date('2010-2-23 9:42:11','yyyy-mm-dd hh24:mi:ss') insettime, 2 status from dual
)
SELECT insettime "时间",sub_time "时间差(秒)"
FROM (SELECT insettime,
(insettime - lag(insettime) over(ORDER BY insettime)) * 86400 sub_time,
status
FROM (SELECT insettime, status, lag(status) over(ORDER BY insettime) pre_status FROM test)
WHERE pre_status IS NULL OR
status <> pre_status)
WHERE status = '2';