CODEWITHSUNDEEP
sqltime-seriesquestdb
Javier Ramirez
Javier Ramirez

Reputation: 4032

QuestDB IN Time Range operator with timestamp relative to today

I am trying to use a query like these ones on QuestDB documentation, using the IN time range operator.

I have this query that works fine

SELECT * FROM trades WHERE timestamp IN '2023-12-22T10:43:00;5s;-1d;7';

I am getting all the rows within second 10:43:00.000 to 10:43:05.999 today, and also for the past 7 days.

My problem is that I want to do this relative to the current timestamp dynamically, and all the examples work with a literal string. How could I do this in a simple way?

Upvotes: 1

Views: 238

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 4032

It turns out a function can be used to compose the literal, so you can use a combination of functions to get your results.

First you want to compose the "reference" timestamp and the pattern after the ; on the IN operator, so you would need to concatenate the dynamic part of the expression relative to the current timestamp, with the fixed time-range pattern.

CONCAT(TO_STR(NOW(), 'yyyy-MM-ddTHH:mm:ss'), ';5s;-1d;7')

That would extract from the current timestamp the string up to the desired resolution, which is seconds in this case.

That would look OK, but there is a nuance. In the question we want to see what happened in the past 5 seconds, plus the same in the last 7 days. With this approach, the query would return only what happened in the current second for today, and what happened in the 5 seconds before the timestamp in the last 7 days.

So what you need to do is subtracting 5 seconds from the current timestamp, as in:

CONCAT(TO_STR(DATEADD('s', -5, NOW()), 'yyyy-MM-ddTHH:mm:ss'), ';5s;-1d;7');

SELECT * FROM trades 
WHERE timestamp IN 
   CONCAT(
      TO_STR(
         DATEADD('s', -5, NOW()), 'yyyy-MM-ddTHH:mm:ss'
      ), ';5s;-1d;7'
   );

Which gives us the desired result.

Upvotes: 0

Related Questions