On my portal I have a chart (devexpress) which uses an API to load JSON records.
In my example I have a sensor which inserts a new record into the database every 60 seconds with following fields: id, serialnumber, tag_id, timestamp, value. So on any one day I have about 1440 datapoints in the database.
On the chart I have a datepicker, which I can choose a specific time range. Now when I choose, "last month", my AJAX call will send the 2 timestamps (now and now - 1 month) to the API.
So I will get 30 x 1440 records for my chart. This is too many. Now I need a solution, that when I chose a 7 day period, I get one record for each hour or when I chose last month I get just one record every day as an average of all the records for the day.
My SQL query on the API is:
SELECT * FROM `records` WHERE `timestamp` BETWEEN "' . $posts['from'] . '" AND "' . $posts['to'] . '"'
You need to add GROUP BY to your sql. In following example you will find exact solution for your needs.
Grouping timestamps in MySQL with PHP