这种解决方案将是一种有效的方法

with pd as (
select
    id,
    max(date) filter (where c.old_value = '0') as "prev",
    max(date) filter (where c.old_value = '1') as "new"
from
    table
group by
    id )
select
    id ,
    new - prev as diff
from
    pd;

如果你需要连续读数之间的差异，类似这样的东西应该会起作用

select a.id, a.new_val, a.date - b.date
from my_table a join my_table b 
     on a.id = b.id and a.prev_val = b.new_val

您可以使用最小/最大子查询。例如：

SELECT mn.id, (mx.maxdate - mn.mindate) as "duration",
FROM (SELECT id, max(date) as mindate FROM table GROUP BY id) mn
JOIN (SELECT id, min(date) as maxdate FROM table GROUP BY id) mx ON
   mx.id=mn.id

如果您在将工期转换为小时数时需要帮助，请告诉我。

你可以使用lead（）/lag（）window functions来访问下一行/上一行的数据。你可以进一步subtract timestamps来给予一个interval并提取所需的部分。

select id, floor( extract('day' from diff)*24 + extract('hour' from diff) ) "Time Difference: Hours" 
  from (select id, date_ts - lag(date_ts) over (partition by id order by date_ts) diff
          from example
       ) hd
  where diff is not null
  order by id;

注意事项：

所显示的预期结果不正确。结果分别为-1和-24。
DATE是一个非常糟糕的列名选择。它既是Postgres数据类型（最多导致混淆），又是SQL标准保留字。