In questDB retrieve the record which is having the closest to range of timestamps

I am new to QuestDB, and I think basic SQL commands run on it. I want a scenario where input is 5 timestamps (say T1,T2,T3...T5) in a single query I want to get the result from DB where timestamp is just-less-than-or-equal-to T1,T2,T3,T4 and T5.

Solution

If you want to have just one row which is smaller than all of the timestamps you can do something like

SELECT timestamp FROM table
WHERE timestamp <= '2018-01-01T02:00:00.000000Z' AND 
timestamp <= '2018-01-01T01:01:00.000000Z' AND 
timestamp <= '2018-01-01T02:07:00.000000Z' AND 
timestamp <= '2018-01-01T01:15:00.000000Z' AND 
timestamp <= '2018-01-01T01:11:00.000000Z'
LIMIT -1

With LIMIT -1 you are asking to return only the last of the records, and since in QuestDB the records are sorted by designated timestamp, you will get the closest record that match your conditions.

There is a similar operation that works for joining two tables by closest timestamp. It is call an ASOF JOIN and in that case it takes two tables and joins each row from one with the row from the other with closest (equal or earlier) timestamp. If you want strictly earlier timestamp join, you can use the LT JOIN.

(Edited after clarification in comments)

You could do an ASOF JOIN with your timestamps by first converting your timestamps into a "pseudo-table" by doing a UNION, then ASOF joining with the table where you have the data. Example below:

with times AS (
  (SELECT * FROM (
  SELECT CAST('2023-01-01T02:00:00.000000Z' as timestamp) AS ts FROM long_sequence(1) 
  UNION
  SELECT CAST('2023-01-01T01:01:00.000000Z' as timestamp) AS ts  FROM long_sequence(1) 
  UNION
  SELECT CAST('2023-01-01T02:07:00.000000Z' as timestamp) AS ts FROM long_sequence(1)  
  UNION  
  SELECT CAST('2023-01-01T01:15:00.000000Z' as timestamp) AS ts FROM long_sequence(1)  
  UNION 
  SELECT CAST('2023-01-01T01:11:00.000000Z' as timestamp) AS ts FROM long_sequence(1) 
  ) ORDER BY ts) timestamp(ts)
)
select * from times asof join trades;

Note I first define a UNION with the five values, and I indicate the designated timestamp is the ts column. With that I can already do ASOF join with any other table where I have a designated timestamp. For each result of the table on the left, I will get the row with equal or closest previous timestamp available.