CODEWITHSUNDEEP
postgresqldate-differencesql-timestamp
RonZ
RonZ

Reputation: 853

PostgreSQL - Date difference between two rows in complex query

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.

Upvotes: 3

Views: 3798

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 3

Related Questions