Get periods by condition

TimescaleDB and PostgreSQL
1

Is there a way to generate Periods by a condition where the value is above a certain threshold without loading all DbValues in memory?

So when I have the following DbValues in my database:

TimeStamp | NumericValue
-------- | -------- | --------
2023-04-01 02:00:00.000 | 8
2023-04-01 02:00:01.000 | 3
2023-04-01 02:00:02.000 | 4
2023-04-01 02:00:03.000 | 6
2023-04-01 02:00:04.000 | 3
2023-04-01 02:00:05.000 | 2
2023-04-01 02:00:06.000 | 9

And I query for NumericValue less than 5 I want to get two instances of Period, one with StartDate=2023-04-01 02:00:01.000 and EndDate=2023-04-01 02:00:02.000 and one with StartDate=2023-04-01 02:00:04.000 and EndDate=2023-04-01 02:00:05.000 but without loading all DbValues from the database in memory.

2

Welcome @pkuehnel :wave:

Have you tried min(timestamp) and max(timestamp) to have access to the range?

select min(Timestamp), max(timestamp) from table where numericValue < 5

Also, you can take a look on toolkit state_agg that maybe help you to create a state from your condition.