2

I have a table with a lot of entries in pairs, related to one another by a field name, each entry has a timestamp timestamp_received. I'd like to get the delta of the timestamp per name

table:

|id|name|timestamp_received|
|1|A|2016-09-21 11:11:00|
|2|A|2016-09-21 11:11:10|
|3|B|2016-09-21 11:11:20|
|4|B|2016-09-21 12:12:21|

desired output:

|name|delta|
|A|00:00:10|
|B|01:01:01|

What I have tried so far:

SELECT name, timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) delta
FROM message;

Which does not produce the expected output

SELECT fpm_left.name, age(fpm_right.timestamp_received, fpm_left.timestamp_received) 
FROM
  (SELECT * FROM message WHERE id IN (SELECT min(id) AS id FROM message GROUP BY name)) fpm_left
JOIN (SELECT * FROM message WHERE IN in (SELECT max(id) AS id FROM message GROUP BY name)) fpm_right
    ON fpm_left.name = fpm_right.name AND fpm_left.id != fpm_right.id;

Which produces the expected output, but seems a little innefficient

Can this be done more elegantly?

2
  • And if there are 3 or more rows for an id? What should the output be? Sep 21, 2016 at 12:09
  • A bug report! There's monitoring and testing around more than three rows existing with the same name, so I'll suck up any behaviour tbh
    – beresfordt
    Sep 21, 2016 at 12:15

2 Answers 2

6

You can use the solution with the window function, you just need to filter out the "other" row. This can e.g. be done using Postgres' distinct on ()

SELECT distinct on (name) 
       name, 
       timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) as delta 
FROM message
order by name, timestamp_received desc;

This will always pick exactly one row per name (the one with the latest timestamp_received), even if there are more then two rows per name.

0
-1

This will return the difference from first and last timestamp_received by name

SELECT distinct 
    name,
    datediff(ms,
        (select timestamp_received 
         from message 
         where name = m.name 
         order by timestamp_received desc),
        (select timestamp_received 
         from message 
         where name = m.name 
         order by timestamp_received asc)
    ) as delta
FROM message m
3
  • 2
    I think this would throw an error instead (in the first round, saying that datediff() is not a function). Sep 21, 2016 at 12:52
  • my fault.. datediff need a first argument of datepart.. 'datediff(ms, date1, date2)
    – Pelice
    Sep 21, 2016 at 14:10
  • 2
    Sure. Did you notice that this question is about PostgreSQL? Sep 21, 2016 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.