Re: join tables by nearest timestamp

Many thanks Achilleas. I did not think to use an outer join in combination
with Distnct and order by clauses, which seems to be the key to my problem.

I slighly adapted your proposal to match my DB schema, but also to select
the real nearest point (and not the nearest one after). I defined a
function 'abs' that computes the absolute value from a timestamp and the
query looks like :

SELECT DISTINCT ON (l1."ID") (l1."Time"-l2."Time") as time_diff, l1.*,l2.*
from
"KnxBusAccess" l1
LEFT OUTER JOIN
"KnxBusAccess" l2
ON ('t')
where
l1."ToGroupAddress" = '2/0/1' AND
l1."Time" >= (now()-interval '1 day') AND
l2."ToGroupAddress" = '2/5/1' AND
l2."Time" >= (now()-interval '1 day')
order by l1."ID", abs(l2."Time"-l1."Time")

the "l1."Time" >= (now()-interval '1 day')" and "l2."Time" >=
(now()-interval '1 day')" are thereto use an index in order to limit the
values to an acceptable range (I have years of records and with an outer
join and without this, the query never finishes).

Thannks, this solves my issue.

Regards,
Brice

2017-11-01 9:11 GMT+01:00 Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>:

> On 01/11/2017 10:06, Achilleas Mantzios wrote:
>
>> On 01/11/2017 07:53, Brice André wrote:
>>
>>> Dear all,
>>>
>>> I am running a postgresql 9.1 server and I have a table containing
>>> events information with, for each entry, an event type, a timestamp, and
>>> additional information.
>>>
>>> I would want to write a query that would return all events of type 'a',
>>> but each returned entry should be associated to the neraest event of type
>>> 'b' (ideally, the nearest, non taking into account if it happened before or
>>> after, but if not possible, it could be the first happening just after).
>>>
>>> By searching on the web, I found a solution base on a "LEFT JOIN
>>> LATERAL", but this is not supported by postgresql 9.1 (and I cannot update
>>> my server) :
>>>
>>> SELECT *
>>> FROM
>>> (SELECT * FROM events WHERE type = 'a' ) as t1
>>> LEFT JOIN LATERAL
>>> (SELECT * FROM events WHERE type = 'b' AND timestamp >= t1.timestamp
>>> ORDER BY timestamp LIMIT 1) as t2
>>> ON TRUE;
>>>
>>> Any idea on how to adapt this query so that it runs on 9.1 ? Or any
>>> other idea on how to perform my query ?
>>>
>> smth like :
>>
>> SELECT l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT
>> OUTER JOIN logging l2 ON ('t') where l1.category='vsl.login' AND
>> (l2.category IS NULL OR l2.category='vsl.SpareCases') AND (l2.logtime IS
>> NULL OR l2.logtime>=l1.logtime) order by l1.logtime;
>>
> oopss, sorry I forgot, you'll have to add a DISTINCT ON and order by
> l2.logtime in order to have what you want :
>
> SELECT DISTINCT ON (l1.logtime,l1.category,l1.username,l1.action)
> l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN
> logging l2 ON ('t') where l1.category='vsl.login' AND (l2.category IS NULL
> OR l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR
> l2.logtime>=l1.logtime) order by l1.logtime,l1.category,l1.user
> name,l1.action,l2.logtime;
>
>
>
>
>>
>>> Thanks in advance,
>>> Brice
>>>
>>
>>
>>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>