Find a closest match in the same table
You can use a CROSS JOIN LATERAL ( ... LIMIT 1 ) to accomplish this, along with btree_gist and &lt;-&gt;

CREATE EXTENSION IF NOT EXISTS btree_gist;

SELECT f1.id AS base, f2.id AS after
FROM foo AS f1
CROSS JOIN LATERAL (
  SELECT id, ts
  FROM foo AS f2
  WHERE f2.ts BETWEEN f1.ts+'15 minutes' AND f1.ts+'25 minutes'
  ORDER BY f1.ts+'20 minutes' &lt;-&gt; f2.ts ASC
  LIMIT 1
) AS f2(id,ts);

 base | after 
------+-------
    1 |     3
    2 |     4
    3 |     5
(3 rows)


There are a lot of tricks going on here. We use &lt;-&gt; as a distance operator for your "closest to". Essentially, it can use a GiST index to determine this with KNN. We also use a CROSS JOIN LATERAL ... LIMIT 1 to get the list of all potential roles, and then limit it to just the one that is "closest to" as determined by our knn operator above.

This will work fastest with a gist index on ts.

CREATE INDEX ON foo USING gist ( ts );


See also another question using &lt;-&gt; on a time stamp