I want to get data from a table for the last 15 minutes of activity.

I know I could do:

SELECT * FROM my_tb
WHERE timestamp > dateadd('m', -15, now());

But that would give me the last 15 minutes, not the last 15 minutes of activity in my table. Supposing the last timestamp recorded in my table was

2025-03-23T07:24:37.000000Z

I could do something like

SELECT * FROM my_tb
WHERE timestamp > dateadd('m', -15, '2025-03-23T07:24:37');

But in that case I need to first find the latest date and then issue this query, so I would need to do two trips to the database.

I tried to do everything on a single query

SELECT * FROM my_tb
WHERE timestamp > dateadd('m', -15, (SELECT max(timestamp) FROM my_tb));

But I get the error there is no matching function dateadd with the argument types: (CHAR, INT, CURSOR)

I have a workaround with a CROSS JOIN but it is very inefficient. on a large table

with ts AS
(SELECT max(timestamp)as ts FROM my_tb)
SELECT * FROM my_tb cross join ts
WHERE timestamp > dateadd('m', -15, ts);

I could always speed it up by limiting the main table to seach over a recent slice of data, for example one week, but in that case it wouldn't work if my data is older than 7 days. For my use case, I would never have data older than 3 or 4 days, so this could work, but I am not sure it is the best way to achieve it and it still very slow.

with ts AS
(SELECT dateadd('m', -15, timestamp) as boundary FROM my_tb limit -1),
last_5_days AS
(SELECT * FROM my_tb
WHERE timestamp > dateadd('d', -5, now()))
SELECT * from last_5_days  join ts
 ON timestamp > boundary;

I want to get data from a table for the last 15 minutes of activity.

I know I could do:

SELECT * FROM my_tb
WHERE timestamp > dateadd('m', -15, now());

But that would give me the last 15 minutes, not the last 15 minutes of activity in my table. Supposing the last timestamp recorded in my table was

2025-03-23T07:24:37.000000Z

I could do something like

SELECT * FROM my_tb
WHERE timestamp > dateadd('m', -15, '2025-03-23T07:24:37');

But in that case I need to first find the latest date and then issue this query, so I would need to do two trips to the database.

I tried to do everything on a single query

SELECT * FROM my_tb
WHERE timestamp > dateadd('m', -15, (SELECT max(timestamp) FROM my_tb));

But I get the error there is no matching function dateadd with the argument types: (CHAR, INT, CURSOR)

I have a workaround with a CROSS JOIN but it is very inefficient. on a large table

with ts AS
(SELECT max(timestamp)as ts FROM my_tb)
SELECT * FROM my_tb cross join ts
WHERE timestamp > dateadd('m', -15, ts);

I could always speed it up by limiting the main table to seach over a recent slice of data, for example one week, but in that case it wouldn't work if my data is older than 7 days. For my use case, I would never have data older than 3 or 4 days, so this could work, but I am not sure it is the best way to achieve it and it still very slow.

with ts AS
(SELECT dateadd('m', -15, timestamp) as boundary FROM my_tb limit -1),
last_5_days AS
(SELECT * FROM my_tb
WHERE timestamp > dateadd('d', -5, now()))
SELECT * from last_5_days  join ts
 ON timestamp > boundary;

• database
• time-series
• questdb

1 Answer 1

The plan to filter with a subquery is the best here, but since QuestDB supports correlated queries on very limited places, we need to revamp a bit the query above

SELECT *
FROM my_table
WHERE timestamp >= (select dateadd('m', -15, timestamp) from my_table limit -1);

QuestDB supports correlated subqueries when asking for a timestamp if the query returns a scalar value. Using limit -1 we get the latest row in the table (sorted by designated timestamp), and we apply the dateadd function on that date, so it needs to be executed just once. If we placed the dateadd on the left, the calculation would need to be applied once for each row on the main table. This query should return in just a few milliseconds, independently of table size.