I have a table with timestamped data. For all the rows, I want to find the row that is the closest to 20 minutes after and between 15 and 25 minutes after.
e.g. if the table is like
CREATE TABLE foo(id,ts) AS VALUES ( 1::int, '2017.10.27T10:12:15'::timestamp with time zone ), ( 2, '2017.10.27T10:24:17' ), ( 3, '2017.10.27T10:30:22' ), ( 4, '2017.10.27T10:40:12' ), ( 5, '2017.10.27T10:52:16' ), ( 6, '2017.10.27T10:53:11' );
I then run a query
select t1.id as base t2.id as after from table t1, table t2 where ??
I would like to get the answer:
base after 1 3 2 4 3 5
For 4,5 and 6 as base I get no result as there are no rows matching with my criteria
It would be easy enough to get this for a given timestamp:
select id from table where timestamp > $mytimestamp+'00:15:00' and timestamp < $mytimestamp + '00:25:00' order by abs(extract ( epoch from (timestamp -($mytimestamp + '00:20:00'))) limit 1;
But how to do this for all the rows in a table?