questdbasof-join

ASOF JOIN is joining too much in the past

I want to join to tables by timestamp. At the moment timestamps are aligned, but at some point in the future they might be slightly unaligned. I can use questdb AS OF JOIN, but this will join each row with the row matching exact timestamp or with the closest earlier timestamp on the second table. I would like to have a limit, and join only within a particular date. This is what I have so far.

SELECT inverter_1.timestamp,
avg(inverter_1.ac_power) AS "Inverter 1",
avg(inverter_4.ac_power)
FROM inverter_1 
ASOF JOIN inverter_4 WHERE inverter_1.timestamp BETWEEN '2024-01-17T00:29:15.051Z' AND '2024-01-17T00:30:15.051Z'
SAMPLE BY 1s;
Solution

  • Got it!

    I can limit how much I want to go in the past for the second table before doing the ASOF JOIN

    
SELECT inverter_1.timestamp,
avg(inverter_1.ac_power) AS "Inverter 1",
avg(inverter_4.ac_power)
FROM inverter_1 
ASOF JOIN (inverter_4 where timestamp BETWEEN '2024-01-17T00:29:15.051Z' AND '2024-01-17T00:30:15.051Z'  )
WHERE inverter_1.timestamp BETWEEN '2024-01-17T00:29:15.051Z' AND '2024-01-17T00:30:15.051Z'
SAMPLE BY 1s;