Timestamp/hstore query?

[Brent Wood <Brent.Wood@xxxxxxxxxx>]

Hi,

I have a database with instrument readings stored in hstore key-value pairs, so a record has a timestamp attribute and an hstore attribute with all the sensor readings for that time. The key identifies the sensor, the value is the reading.

Not all sensors have a reading at every timestamp.

Timestamps are 1 sec intervals.

The db uses Timescaledb hypertables to partition the data

How can I select the LAST (hstore) value for each instrument (hstore key) at one minute intervals. So I get an output of 1 minute timestamps & an hstore containing the last key-value pairs for each key in present in that 1 minute interval.

Essentially this provides a snapshot saying "at this time (1 min interval) the latest (current) reading for each sensor. was this value"

I'm OK with a user defined pl/pgsql function if this is the best approach.

Thanks

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitter Instagram

To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems