Is there any way to do sampling on time series data in PostgreSQL (v11) based on a fixed time interval?
For example, we are storing 1 record per second for 24 hours in a given table. This means approximately 86400 records are in the table per day.
Is there any simpler way to retrieve a data point for every 10 seconds of data? The query should return 8640 records.
Currently, I have implemented a solution using a window function. But for larger data sets it is taking too much time.
SELECT message_date, message FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY date_trunc('minute', message_date) ORDER BY message_date DESC ) AS r, t.* FROM rdm.telemetry_2021_01_21 t WHERE device_id = 'CP2' AND message_date >= '1/21/2021 01:00:00 AM' AND message_date <= '1/21/2021 11:00:00 PM' ) x WHERE x.r <= 1 ORDER BY message_date DESC;
The above solution supports sampling for 1 sec, 1 min or 1 hr depending on which values is provided in the
date_trunc options. And also, this query takes too much time against a table that contains millions of records.
"Explain analysis" result
Can anyone please suggest some other option/solution? Maybe using lateral join?