# Query timestamp gaps, grouped by a Column value

**URL:** <https://community.questdb.com/t/query-timestamp-gaps-grouped-by-a-column-value/218>\
**Category:** Community\
**Created:** [October 29, 2024, 4:02pm UTC](https://community.questdb.com/t/query-timestamp-gaps-grouped-by-a-column-value/218 "2024-10-29T16:02:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lars\_m](https://avatars.discourse-cdn.com/v4/letter/l/e47c2d/32.png) [@Lars\_m](https://community.questdb.com/u/Lars_m)\
**Post date:** [October 29, 2024, 4:02pm UTC](https://community.questdb.com/t/query-timestamp-gaps-grouped-by-a-column-value/218/1 "2024-10-29T16:02:07Z")

</div>

Hello,

I would like to check a table for unexpected data time-gaps, grouped by a column.

For example is there a query for an input table:

```auto
Datetime, Sym
(timestamp) (symbol)

2024-10-01 08:00:00 AA
2024-10-01 08:00:00 CC
2024-10-01 08:00:00 BB
...
2024-10-01 08:01:00 AA
2024-10-01 08:01:00 BB
2024-10-01 08:01:00 CC
...
2024-10-01 08:02:00 AA
2024-10-01 08:03:00 BB
2024-10-01 08:04:00 CC

```

which yields result:

```auto
Datetime, Sym, Time_gap
2024-10-01 08:04:00 CC 3
2024-10-01 08:03:00 BB 2
2024-10-01 08:02:00 AA 1
...
2024-10-01 08:01:00 AA 1
2024-10-01 08:01:00 BB 1
2024-10-01 08:01:00 CC 1

```

For each ‘Sym’ the time-gap between consecutive rows is calculated as ‘Time\_gap’, with the timestamp of the event, and sorted DESC by ‘Time\_gap’.

Note I showed ‘Time\_gap’ as an integer (minutes), but any numeric value (seconds, timestamp) would suffice.

Thanks!

---

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [October 29, 2024, 5:09pm UTC](https://community.questdb.com/t/query-timestamp-gaps-grouped-by-a-column-value/218/2 "2024-10-29T17:09:22Z")

</div>

sure is!

You can use the first\_value() window function to get the timestamp from the row before partitioned by Sym. At the moment we need a workaround, as the timestamp data type is not supported for this window function, but please do vote the [github issue](https://github.com/questdb/questdb/issues/4395) to raise awareness 🙂

You could do this to check gaps in all the rows for today. I am displaying the gap in both seconds and millis for completeness. Then you could add a condition to show only those above a certain threshold.

```auto
WITH time_and_prev AS (
  SELECT Datetime, Sym, first_value(Datetime::long) OVER (PARTITION BY Sym ORDER BY Datetime ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS prevTimestamp 
FROM table where timestamp IN today()
)
SELECT Datetime, Sym, prevTimestamp::timestamp, datediff('s', Datetime, prevTimestamp::timestamp) as gap_s, (Datetime - prevTimestamp::timestamp) / 1000 as gap_ms FROM time_and_prev ;

```

---

<div class="post-metadata">

**Author:** ![Lars\_m](https://avatars.discourse-cdn.com/v4/letter/l/e47c2d/32.png) [@Lars\_m](https://community.questdb.com/u/Lars_m)\
**Post date:** [October 31, 2024, 11:17am UTC](https://community.questdb.com/t/query-timestamp-gaps-grouped-by-a-column-value/218/3 "2024-10-31T11:17:38Z")

</div>

Thanks Javier so much for the quick & thorough response!

For this section of your query (I forgot to specify my table’s name, so your query has it as ‘table’) :

```auto
FROM table where timestamp IN today()

```

I assume you meant:

```auto
FROM table where Datetime IN today()

```

Unfortunately this gives an error: “unknown function name: today()”.  
I’m not clear why today() didn’t work, but I just replaced the today() call with a str constant (eg: ‘2024’).

---

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [October 31, 2024, 3:02pm UTC](https://community.questdb.com/t/query-timestamp-gaps-grouped-by-a-column-value/218/4 "2024-10-31T15:02:37Z")

</div>

You are correct, sorry I messed up the column names!

The `today()` function was recently added. It might be the case you are running an older version of questdb. In any case, the important part is on the window function, for the `where` filter you can just use anything you might need.