Timestamp/hstore query?

[Brent Wood]

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
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png][https://niwa.co.nz/sites/niwa.co.nz/files/ETNZ_Official-supplier-logo-75h.png]<https://www.niwa.co.nz>
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<https://www.niwa.co.nz> 
Facebook<https://www.facebook.com/nzniwa> 
LinkedIn<https://www.linkedin.com/company/niwa> 
Twitter<https://twitter.com/niwa_nz> 
Instagram<https://www.instagram.com/niwa_science>
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