Detect unchanging values for fields in one table

Get Help
1

I have a table with the fields

id, timestamp, value

I want to check all the values are changing for all id’s with the last 48 hours and show the id for the ones that are not changing.

Does anyone know the query I could use to check this ?

I was thinking trying to join two queries, one selecting an hour range of values 48 hours ago and the other selecting the values from the last hour.

However, I am not sure on the details.
Is there a better way ?

Thanks

2

Hi @glennpierce
Yes, sounds like a JOIN would do the trick. Or a sub-select. Depends on which database you’re querying.
If you want help with the query, then you can probably find very good examples on stackoverflow.com - otherwise you should post your current query.

3

I think I have it going. For reference

SELECT t1.sensor_id, MAX(t1.ts), MAX(t2.ts) FROM
(SELECT
sensor_id,
ts,
value
FROM sensor_values
WHERE ts >= (NOW() + INTERVAL ‘-48 hour’) :: TIMESTAMPTZ
AND ts <= (NOW() + INTERVAL ‘-47 hour’) :: TIMESTAMPTZ
AND sensor_id IN (SELECT id
FROM sensors
WHERE name LIKE ‘bi%_pv’)
) AS t1
INNER JOIN (

SELECT
sensor_id,
ts,
value
FROM sensor_values
WHERE ts >= (NOW() + INTERVAL ‘-1 hour’) :: TIMESTAMPTZ
AND sensor_id IN (SELECT id
FROM sensors
WHERE name LIKE ‘bi%_pv’)
) AS t2
ON t1.sensor_id = t2.sensor_id WHERE t1.value = t2.value GROUP BY t1.sensor_id;

1 Like