Counting entries based on timestamps

Community
1

Hi!

I’m trying to create a query to get some entries in my QuestDB based on some time ranges. The table I have is defined as

sessionId (string) | startTime (timestamp) | endTime (timestamp)

On any timerange given, which i assume I have to compute using the timestamp_sequence function, i want to know the number of alive sessions in that time. An alive session is a session which started (startTime) before or in the given timestamp and ended (endTime) after the given timestamp.

For example, for the given data:

123 | 2025-06-13T09:00:00 | 2025-06-13T09:02:00
456 | 2025-06-13T09:01:00 | 2025-06-13T09:03:00
789 | 2025-06-13T09:05:00 | 2025-06-13T09:10:00
987 | 2025-06-13T09:08:00 | 2025-06-13T09:10:00
654 | 2025-06-13T09:12:00 | 2025-06-13T09:15:00
321 | 2025-06-13T09:16:00 | 2025-06-13T09:18:00

I want:

2025-06-13T09:00:00 | 1
2025-06-13T09:01:00 | 2
2025-06-13T09:02:00 | 1
2025-06-13T09:03:00 | 0
2025-06-13T09:04:00 | 0
...

For now, I have

SELECT
  t.ts,
  count(s.sessionId)
FROM (
  SELECT timestamp_sequence(
    date_trunc('minute', dateadd('h', -6, now())),
    60000000L
  ) AS ts
  FROM long_sequence(60)
) t
LEFT JOIN Sessions s
ON s.startTime <= t.ts AND s.endTime > t.ts
ORDER BY ts;

While the inner query seems to correctly return each minute from an hour ago until now, when running the entire query i get some strange timestamp. What am I doing wrong?

Thanks!

2

timestamp_sequence is stateful. Each time the value is accessed, it is incremented, so it can return values you don’t expect. For example:

SELECT timestamp_sequence(  
    '2025-06-13T09:00:00'::timestamp,
    60000000L
  ) AS ts FROM long_sequence(60) 
  ORDER BY ts;
ts
2025-06-13T09:00:00.000000Z
2025-06-13T09:02:00.000000Z
2025-06-13T09:04:00.000000Z
2025-06-13T09:06:00.000000Z
2025-06-13T09:08:00.000000Z
2025-06-13T09:10:00.000000Z

It is accessed for the SELECT and for the ORDER BY, hence you iterate 2 minutes at a time.

The quickest (and admittedly, ugly) fix is just to materialise the timestamps in a table and join against that instead:

CREATE TABLE timestamps AS (
   SELECT timestamp_sequence(
    date_trunc('minute', dateadd('h', -6, now())),
    60000000L
  ) AS ts
  FROM long_sequence(60)
);
3

Ok, thanks!

Is it possible to create in some way temporary tables like in postgresql?

4

No, but you could try a window function workaround:

SELECT ('2025-06-13T09:00:00'::timestamp + (x * 6_000_000_0L)) as ts FROM (
  SELECT x - 1 as x FROM long_sequence(61)
)
ORDER BY ts;
5

You can get the dense range of time intervals with this

select startTime, first(startTime) from Sessions  
sample by 1m from '2025-06-16T01:15' to '2025-06-16T04:15'

Note I am adding a cheap aggregation (first), as otherwise SAMPLE BY will complain.

So your query would become

SELECT
  t.ts,
  count(s.sessionId)
FROM (
  select startTime ts, first(startTime) from Sessions  
sample by 1m from '2025-06-16T01:15' to '2025-06-16T04:15'
) t
LEFT JOIN Sessions s
ON s.startTime <= t.ts AND s.endTime > t.ts
ORDER BY ts;
6

Note you could also use a dynamic date, as you were doing before


SELECT
  t.ts,
  count(s.sessionId)
FROM (
  select startTime ts, first(startTime) from Sessions  
  sample by 1m 
  from 
      date_trunc('minute',dateadd('h', -6, now())) 
   to  
     date_trunc('minute',now())
) t
LEFT JOIN Sessions s
ON s.startTime <= t.ts AND s.endTime > t.ts
ORDER BY ts;
7

I’ve tried the both the solutions. I get the error FROM-TO intervals are not supported for keyed SAMPLE BY queries when performing any query like @javier suggested.

In the end I’ve done something like the following, getting the correct data:

SELECT
  t.ts,
  count(s.sessionId)
FROM (
  SELECT (dateadd('m', -60, now()) + (x * 6_000_000_0L)) as ts FROM (
  SELECT x - 1 as x FROM long_sequence(61)
)
ORDER BY ts
) t
LEFT JOIN Sessions s
ON s.startTime <= t.ts AND s.endTime > t.ts
ORDER BY ts;

Now for the next step: I need to include this in a Grafana graph, letting the user decide the range of time to visualize. How should I update the query to get the range from Grafana?

Thanks!

8

In that case, you can add a WHERE clause and pass $__timefilter or $timeFrom + $timeTo. This will be populated by Grafana based on the time filter button.

I think we will add generate_series to make this timestamp generation more ergonomic in future. Look out for it!

1 Like
9

Ok thanks a lot! I will give it a try and will post the final solution afterwards.

1 Like
10

Hopefully it can be merged for next release, or the one after.

1 Like
11

Wow thanks a lot! I will absolutely give it a try when it will be merged.