From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Brent Wood <Brent(dot)Wood(at)niwa(dot)co(dot)nz> |
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 18:32:22 |
Message-ID: | CAHOFxGqbjSOuzzTwDAG=YEQ07rd-WszvBOq47PAiTLZNZwdjqA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Brent Wood | 2021-04-14 21:33:03 | Re: Timestamp/hstore query? |
Previous Message | Tom Lane | 2021-04-14 14:41:00 | Re: Question about PG_CONTROL_VERSION 1 |