PostgreSQL - Date difference between two rows in complex query

Question

I've got this query:

SELECT apple, banana, update_at FROM 
(
    SELECT DISTINCT ON (apple) *
        FROM table
            WHERE apple IN ('ad', 'sa')

        ORDER BY apple, update_at DESC
) q

The purpose of this query is to get the rows that has apple "ad" or "sa", and return the most updated row (update_at (timestamp)). This query works.

My problem is that I want that instead of the update_at column, the query will show the difference between the timestamps (duration) of the query matched row and the row which preceded it.

Example:

apple---|banana---|update_at
============================
ad------|VAL------|2017-06-01 12:12:30
ad------|VAL------|2017-06-01 09:00:00
ad------|VAL------|2017-05-31 13:41:00

Result:

apple---|banana---|update_at
============================
ad------|VAL------|**03:12:30**

03:12:30 is the duration.

Hope that it clear enough.

score 3 · Accepted Answer · 2017-06-01 15:12:59Z

3

You need to use window functions for this:

SELECT apple, 
       banana, 
       update_at, 
       prev_update_at, 
       update_at - prev_update_at as duration
FROM (
   SELECT *, 
          row_number() over (partition by apple order by update_at desc) as rn, 
          lag(update_at) over (partition by apple order by update_at) as prev_update_at
   FROM table
   WHERE apple IN ('ad', 'sa')
) q
where rn = 1;

edited Jun 1, 2017 at 15:12

answered Jun 1, 2017 at 14:56

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

@RonZ: sorry, wrong order by for the lag() function.

Collectives™ on Stack Overflow

PostgreSQL - Date difference between two rows in complex query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related