TimescaleDB query to select rows where column value changed from previous row

Question

Just recently started using TimescaleDB with Postgres to handle most requests for data.

However I'm running into an issue where I have a horribly inefficient request for time series of data.

It's a data series that can be any length of time, with specific Integer values.

Most of the time the value will be the same unless there's an anomaly. So rather than fetching +10,000 rows of data. I would like to aggregate this into "time blocks".

Let's say there 97 items in a row where the value is 100 (new item for every 5 minutes) #98 the value is 48 for 5 items in a row and then it goes back up to 100 for another 2,900 rows.

I don't want to fetch 3002 items to display this data. I should only need to fetch 3 items.

• 1 item that says the value is 100 from a startDate
• 1 item that says the value is 48 from a startDate after #1
• 1 item that says the value is 100 again from a startDate after #2

But I'm having some trouble figuring out how I can do this with timescaledb.

basically, if the value is the same as the last value, aggregate it. That's all I need it to do.

Does anyone know how to construct a VIEW for this kind of situation in timescaleDB using continuous aggregation (or if there's a faster way) to fetch this?

Nona

Answer 1

You can achieve the desired result with window functions and a subselect:

SELECT time, value FROM (
  SELECT 
    time,
    value,
    value - LAG(value) OVER (ORDER BY time) as diff
  FROM hypertable) ht 
WHERE diff IS NULL OR diff != 0;

You use a window function to calculate the diff to the previous row and then filter all the rows where the diff is 0 in the outer query.

Hiss

Shouldn't this be where diff is NOT null or diff != 0? – Watersick

No @Catfish, check here - dbfiddle.uk/svS2Ip6y ... Where diff is NULL describes the very first point (change from NULL to the value of the point). If you changed the query to NOT NULL, then the first point wouldn't be regarded. – Filippa