Select nearest timestamp from multiple tables

Community
,
1

I’m trying to select from multiple tables values closest (<=) to the given timestamp. For example in the demo how would I select a few columns from one table and a few columns from another where the timestamp is closest to X. Sorry I’m not as familiar with the demo table data.

2

Hi @jmthomas

You can use ASOF JOIN: ASOF JOIN keyword | QuestDB

If you want to key it i.e. nearest row for each symbol, you can use ON.

SELECT * FROM
(
    (
    SELECT dateadd('d', -1, now()) as t
    UNION
    SELECT dateadd('d', -2, now()) as t
    ) ORDER BY t
)
ASOF JOIN trades;
t symbol side price amount timestamp
2025-07-11T21:41:16.361236Z ETH-USD buy 2973.57 1.681951 2025-07-11T21:41:16.244999Z
2025-07-12T21:41:16.361236Z ETH-USD buy 2937.68 0.745448 2025-07-12T21:41:16.357000Z

If it will just be a single row, then you can just filter for WHERE timestamp <= t ORDER BY timestamp DESC LIMIT 1.

3

Yeah I think I just needed to experiment with it a little. What I was really looking for is something like this:

select t1.timestamp, VALUE1, VALUE2 from TABLE1 as t1 ASOF JOIN TABLE2 LIMIT -1

Where VALUE1 comes from TABLE1 and VALUE2 comes from TABLE2. The LIMIT -1 gives me the latest value from TABLE1 which is matched to the closest (<=) value in TABLE2. Please correct me if I’m wrong here.

4

I also needed the WHERE clause limiting the time:

select t1.timestamp, VALUE1, VALUE2 from TABLE1 as t1 ASOF JOIN TABLE2 where t1.timestamp < ‘2025-07-14T22:20:00’ LIMIT -1

5

Yes, if you are just picking a single row, that’s the way to do it.

If you need to batch up a bunch of these ‘point queries’, you have two options:

  1. Just stream them using Postgres Wire, with the right settings it will get pipelined and sent back in batches.
  2. Combine them into one query with UNION. This only support a limited number by default (maybe 100 or so). It can be configured to more, but may be more trouble than its worth.