how can I get the last value for each key in a set of postgres hstore records?

Question

I have a table of sensor readings as postgres hstore key-value pairs. Each record has a timestamp at 1 sec intervals. Not all sensors record every second.

The table is essentially:

create table readings (
    timer timestamp primary key,
    readings hstore
);

the hstore comprises (<sensor_id> ) key/value pairs for readings taken at the time specified in the timestamp.

eg: "67" "-45.67436", "68" "176.5424" could be key/value pairs representing latitude & longitude, with a timestamp in the timer column.

There would be several lat/lon hstore pairs in a given minute, the query I want would return the last one in the timeseries for that minute (for each key).

I want to retrieve the latest value for each sensor in the hstore at 1 minute intervals.

Essentially: Get the set of timestamps & hstores for that minute. Get the keys from that set. Get the value for that key from the key-value pair with the latest timestamp in the set Return the 1 min timestamp & resulting hstore Repeat for the next minute

I'm happy with a pl/pgsql function if that is the best approach.

Answer 1

for

"the query I want would return the last one in the timeseries for that minute (for each key)"

you can use windows function, also I'm guessing you have different sensors

select * from 
( 
   select * , row_number() over (partition by sensorid, to_char(timer, 'YYYY-MM-DD HH:MM') order by timer desc) rn
) t
where rn = 1

Answer 2

with t(t, h) as (values
    ('2000-01-01 01:01:01'::timestamp, 'a=>1,b=>2'::hstore),
    ('2000-01-01 01:01:02', 'a=>3'),
    ('2000-01-01 01:02:03', 'b=>4'),
    ('2000-01-01 01:02:05', 'a=>2,b=>3'))
select
    date_trunc('minute', t),
    jsonb_object_agg(k, v order by t)
from t, each(h) as h(k,v)
group by date_trunc('minute', t);

┌─────────────────────┬──────────────────────┐
│     date_trunc      │   jsonb_object_agg   │
├─────────────────────┼──────────────────────┤
│ 2000-01-01 01:01:00 │ {"a": "3", "b": "2"} │
│ 2000-01-01 01:02:00 │ {"a": "2", "b": "3"} │
└─────────────────────┴──────────────────────┘

demo