Question 90629 in PostgreSQL Question posted in
The official documentation can be found here.

SQL How to subtract 2 row values of a same column based on same key – Postgresql

How to extract the difference of a specific column of multiple rows with same id?

Example table:

id prev_val new_val date
1 0 1 2020-01-01 10:00
1 1 2 2020-01-01 11:00
2 0 1 2020-01-01 10:00
2 1 2 2020-01-02 10:00

expected result:

id duration_in_hours
1 1
2 24

summary:
with id=1, (2020-01-01 10:00 – 2020-01-01 11:00) is 1hour;

with id=2, (2020-01-01 10:00 – 2020-01-02 10:00) is 24hour

Can we achieve this with SQL?

Tags:

4

Answers


  1. 0

    you could use min/max subqueries. For example:

    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

    Let me know if you need help in converting duration to hours.

    Login or Signup to reply.
  2. 0

    if you need the difference between successive readings something like this should work

    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
    Login or Signup to reply.
  3. 0

    This solutions will be an effective way

    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;
    Login or Signup to reply.
  4. 0

    You can use the lead()/lag() window functions to access data from the next/ previous row. You can further subtract timestamps to give an interval and extract the parts needed.

    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;

    NOTE:
    Your expected results, as presented, are incorrect. The results would be -1 and -24 respectively.
    DATE is a very poor choice for a column name. It is both a Postgres data type (at best leads to confusion) and a SQL Standard reserved word.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search