Postgresql Convert to hours

Question

I have a lag function that works, but I need it to return the difference in hours. When I try the date diff functions I get confused. Any help is appreciated. Thank you

Select
  well_id,
  reported_date,
  lag (reported_date,1) over (
  partition by well_id
  order by well_id
) AS hour_rate
from 
public.production;

Answer 1

Use EXTRACT(EPOCH FROM age(...)) and then calculate the amount of hours:

SELECT well_id, reported_date, 
  (EXTRACT(EPOCH FROM age(reported_date,
                          LAG(reported_date) OVER w))/3600)::int
FROM production
WINDOW w AS (PARTITION BY well_id ORDER BY reported_date 
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);

Demo: db<>fiddle

CREATE TABLE production (well_id int, reported_date date);
INSERT INTO production VALUES 
(1,CURRENT_DATE-1),(1,CURRENT_DATE),(1,CURRENT_DATE+2),
(2,CURRENT_DATE-5),(2,CURRENT_DATE-6),(2,CURRENT_DATE-8);

SELECT 
 well_id, 
 reported_date,
 age(reported_date,LAG(reported_date) OVER w),
 coalesce((
   EXTRACT(EPOCH FROM age(reported_date,LAG(reported_date) OVER w))/3600)::int,0) AS hour_rate 
FROM production
WINDOW w AS (PARTITION BY well_id ORDER BY reported_date 
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY well_id,reported_date;

 well_id | reported_date |  age   | hour_rate 
---------+---------------+--------+-----------
       1 | 2021-08-26    |        |         0
       1 | 2021-08-27    | 1 day  |        24
       1 | 2021-08-29    | 2 days |        48
       2 | 2021-08-19    |        |         0
       2 | 2021-08-21    | 2 days |        48
       2 | 2021-08-22    | 1 day  |        24
(6 rows)