From: | Brent Wood <pcreso(at)yahoo(dot)com> |
---|---|
To: | Brent Wood <brent(dot)wood(at)niwa(dot)co(dot)nz>, Michael Lewis <mlewis(at)entrata(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Timestamp/hstore query? |
Date: | 2021-04-14 21:33:03 |
Message-ID: | 288622710.2086083.1618435983252@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks for your reply,
The table is essentially:create table readings (timer timestamp primary key, readings hstore);
the hstore comprises (<sensor_id> <reading>) 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 don't think your examples will give me the separate hstore key-value pairs, extracted as the last in the interval for each key & reassembled as an hstore list in the result. The sensor id is the hstore key, as described above, not a separate attribute. That said, the keys can be extracted from the hstore much like a column, but I'm not sure that is the best approach.
Treating each set of hstores in an interval as an array & extracting the last elements may be viable. But I['m not sure how...
On Thursday, April 15, 2021, 6:33:08 AM GMT+12, Michael Lewis <mlewis(at)entrata(dot)com> wrote:
If you share example schema and desired output (like a dummy table or even pseudo code SQL), then I'm sure many people could help you. Right now, the description of your desired result seems a bit unclear, at least to me.
If you wanted to run this hourly for the last 1 hour, it sounds a bit like want this-
select sensor_id, date_trunc( 'minute', timestamptz_field_name_here ), last( value_from_hstore ) over ( partition by sensor_id, date_trunc( 'minute', timestamptz_field_name_here ) ) as last_value_recorded
from data_table_here
where timestamptz_field_name_here BETWEEN NOW() - interval '1 hour' and NOW()
group by sensor_id, date_trunc( 'minute', timestamptz_field_name_here )
You could also use the left join "where is null" pattern to check that a prior record in the minute period of time does not exist for that same key. Something like this-
select d1.sensor_id, date_trunc( 'minute', d1.timestamptz_field_name_here ), d1.timestamptz_field_name_here as last_value_recorded
from data_table_here as d1
left join data_table_here as prior_d1 ON prior_d1.sensor_id = d1.sensor_id AND prior_d1.timestamptz_field_name_here < d1.timestamptz_field_name_here and prior_d1.timestamptz_field_name_here >= date_trunc( 'minute', d1.timestamptz_field_name_here )
where d1.timestamptz_field_name_here BETWEEN NOW() - interval '1 hour' and NOW()
>
From | Date | Subject | |
---|---|---|---|
Next Message | Viral Shah | 2021-04-14 21:50:58 | Metric to calculate WAL size left to transfer to Standby |
Previous Message | Michael Lewis | 2021-04-14 18:32:22 | Re: Timestamp/hstore query? |