Getting time delta between two related rows 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?
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.