Postgres Pro
Downloads
Home   >   mailing lists

Timestamp/hstore query? - Mailing list pgsql-general

From Brent Wood
Subject Timestamp/hstore query?
Date
Msg-id SY3PR01MB081006506D32FA0D7823B239A14E9@SY3PR01MB0810.ausprd01.prod.outlook.com
Whole thread Raw
Responses Re: Timestamp/hstore query?  (Michael Lewis <mlewis@entrata.com>)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Dmitry Dolgov
Date:
Subject: Re: Why is writing JSONB faster than just JSON?
Next
From: "m7onov@gmail.com"
Date:
Subject: View invoker privileges