1
I am making a Datalogger system and I would like to know if there is any intrinsic function of Mysql to pick up, on the record search screen, the value in a pre-time intervaldefined, or if I must program to compare whether the timestamp is equal to or immediately superior to the previous one, create an array and include in it the values with that preset time. If this situation is only solved with programming (In my case, I am using PHP), the idea of doing a fetch of all records in the full range of interest and testing the weather condition is the most effective/elegant way to do?
For example, records are stored in the database every 30 seconds, but I wanted to make a graph that only picks up data every hour. So I would take the first record, then I would discard from the graph the following records whose values were less than 1 hour compared to the first filtered record. Then it would insert in the graph the record whose value was 1 hour higher than the second filtered record, and so on. It would be like using limit/offset, but instead of filtering by the record number, it would be by date/time.
Thanks in advance for the support
Precisely set "preset time interval". Will it always be the same range with a fixed start and duration? Can you vary any of them? Will any of them be informed or must be recovered from their database? You only want the ranges that contain some data or want all ranges even if no data?
– anonimo
On the screen, I have a field of type "text" to indicate the quantity and a field of type "select" for the user to select: Records (All records in the selected period), Hours (Records will be displayed from time to time. As the database picks up the data every 30 seconds, I will not display the records on the chart every 30 seconds, but on an hour-by-hour basis), Days (A single record will be displayed per day, whichever is the first of that day, throughout the selected period), Week (A single record per week will be displayed) and Month (One record per month will be displayed).
– fjdoliveira
So, if I choose the "Minutes" option in the "select" and "15" type field in the "text" type, I will want to display the logs every 15 minutes from the start date/time to the end date/time that the user set in the search.
– fjdoliveira
You only want the first record within the time interval you set?
– anonimo
No. These are all records that are in this period according to the defined interval. For example, if the user wants the period between 01/10/2019 at 15:00 and 10/10/2019 at 19:01, with an interval of 15 minutes, the records must be displayed whose record date is 15:00, 15:15, 15:30, 15:45, etc. and will not be displayed every 30 seconds.
– fjdoliveira
I am using the following SQL: SELECT * FROM table WHERE dataHora BETWEEN "2019/10/01 15:00" AND "2019/10/10 19:01" GROUP BY DATE_ADD(DATE_FORMAT(dateHora, '%Y-%m-%d %H %i'), INTERVAL 15 MINUTE) ORDER BY id desc for example, it should be every 15 minutes
– fjdoliveira