How to select fix number of datapoints spread evenly over a time range

Question

I am having a hard time creating a SQLite command that will return an evenly spaced out data points based on time if the number of data points is beyond 50.

Basically, I am having data stored every 30 seconds. However, if I want to see the data in the last hour, that will be a very large amount of data and will end up freezing my RPI as it attempts to visualize this data. So, my solution is to create a SQLite command that will limit the number of return data points to 50 that are spread evenly across a time range.

I have separate commands for if I want last min, 5 mins, 10 mins, etc. Once it goes beyond 1 hour, I need to limit the data so I can hard code this into the command (no need for IF statements)

Here is my currently attempt at the command which is not working:

select Voltage from Battery2 where Timestamp >= Datetime('now', '-1 hour') % (SELECT COUNT(*)/50 FROM Battery2)=0;

This is based on this stack overflow post: How to select fixed number of evenly spread rows in timeseries sqlite database

EDIT:

Here is some sample data from the output of the function:

Voltage: 54
Timestamp: "2022-01-13 16:47:47"

Voltage: 54
Timestamp: "2022-01-13 16:48:18"

Voltage: 54
Timestamp: "2022-01-13 16:48:49"

Answer 1

You can use NTILE() window function to divide the resultset in 50 groups, based on the column Timestamp and then with aggregation pick 1 row from each group with MAX() or MIN() aggregate function:

WITH cte AS (
  SELECT *, NTILE(50) OVER (ORDER BY Timestamp) nt 
  FROM Battery2 
  WHERE Timestamp >= datetime('now', '-1 hour')
)
SELECT MAX(Timestamp) AS Timestamp, Voltage
FROM cte
GROUP BY nt;