Reduce datapoints chart

General
1

I’m query a sqlite database to fill a chart on page load. This works all fine.
However for the battery the chart is 1 month width and filling it with “sampled every 3 minutes” = 14400 point takes to long. It’s also not necessary to have that much points in the chart.
Is it possible to query not all the points from the db?
msg.topic ="SELECT batterij ,timestamp FROM temphal WHERE timestamp >= " + fromdate + " AND timestamp <= " + enddate + "" ;

There was also a node to reduce the datapoints in an array, but I can’t find it any more. Somebody a suggestion?

2

Good thinking – you definitely don’t want to try to show that many points in the chart. I’ve not used sqlite, but if it supports the usual SQL syntax, you want to group your data into bins of time. In your case, I would probably aggregate the data into a single data point per day…

From the docs, it looks like you can use the strftime(fmt, dtm) function to retrieve just the date portion of each data point. Then apply an average to the values returned (or min, max), something like this:

SELECT AVG(batterij), STRFTIME('YYYY-mm-dd', timestamp) FROM temphal WHERE timestamp BETWEEN '2018-05-01' AND '2018-06-01' GROUP BY 2

BTW, it looks like you are using a function node to append all your strings/variables together into the topic – it may be a bit simpler and more readable to use “mustache” substitution syntax in a template node, i.e.

SELECT AVG(batterij) AS batterij,
       STRFTIME('YYYY-mm-dd', timestamp) AS timestamp
  FROM temphal
 WHERE timestamp BETWEEN '{{fromdate}}' AND '{{enddate}}'
 GROUP BY 2
3

Thanks for the suggestion, however 1 point a day is a little to less.
Did some googling my self and came up with this solution.

msg.topic ="SELECT batterij ,timestamp FROM temphal WHERE timestamp >= " + fromdate + " AND timestamp <= " + enddate + " AND ROWID % 20 =0" ;

This will give every 20th row, so this will reduce the datapoints to 700. Not perfect but for a slow changing signal like battery discharge it will do the job. Ideal the 20 rows will be averaged, but to accomplish that I have to play a bit more with sqlite.

Thanks for the template node tip, I will look at it. Using the function the way I’m using it now is because, just like other code, it’s inspired on what you find on the internet and adjust for your own needs.