MySQL Select every nth row

Question

MySQL

Friends, tell me please.
There is a table with three fields - id (auto_increment), date (timestamp) and value .
Every 10 seconds, one value entry is added to the database. The rest of the fields are filled in automatically.

It is necessary to build graphs for an hour, a day, etc.
To build a graph for an hour, you need to select 60 records out of 360. That is, one value is taken for every minute (every 6). Accordingly, per day - 24 records out of 8640 (each 360). And so on…

Is it possible to implement such a query using MySQL using id or date?

Answer 1

If you need to sort data, you can use grouping by date field format:
For example, to group by minutes:
GROUP BY DATE_FORMAT (`date`, "%y-%m-%d-%H-%i")
"%y% j%H-%i" is the same, but it will be faster this way.

Answer 2

And how are you going to build a graph for every 6th value, if you have 6 different (probably?) values, and you will take only one (not even the arithmetic mean)?

Answer 3

select * from tbl where id%10=0 and datetime>=addtime(now(), '-0:1:00');
Select data for the last minute, in which the id is 10. Something similar will happen in a day.

Answer 4

Since such a table can have a very decent volume, which will inevitably suffer speed, I would advise you to implement all the logic at the time of inserting a new record, namely:
1. Create an additional. tables for records of the hour, day, etc., in which to store only records for the corresponding points in time. Then access to them will be very fast. True, the volume will increase.
2. If there is access to the software that writes to the database, check the value of the corresponding interval in it. If not, or if the requirement to “implement using MySQL” is important, then use a trigger to insert into the source table, in which to check the condition and duplicate the record in the tables for the hour, day, etc.
At the same time, it is enough to insert into the additional tables not the entry itself, but only its id.

Answer 5

Select every nth as follows (example for every 60th):
SELECT @row := @row + 1 row, t.id
FROM table t, (SELECT @row := 0) row
GROUP BY t.id
HAVING row % 60 = 0
The problem is obvious: all rows are selected, and then filtering by having. Yes, according to the index, everything should work quickly and filtering at the database level will be faster than on the used jap, but this is clearly not the case.
It is wrong to do this on mysql, there is an orthodox rrdtool , which is designed to work with sequences of values ​​in time, automatically receiving data in the context of some time (for example, a day, a week, a month, a year).

Answer 6

Solution 1. Is the minute timestamp evenly divisible by 60? You can write a comparison condition for MySQL.
Solution 2. Is it possible to write an additional key? If yes, then there is another resource-saving solution.

Answer 7

Select all records for the required interval and from them, as mentioned above, calculate the arithmetic mean of several values, which will be the point of the graph.

Answer 8

Judging by the data task, you will have a lot. I would advise you to add fields and operate on them

That is, if the time values ​​in the
database
records look
like this : :10 then we only take 22:19:00 22:20:00
Here the like will die, it is worth adding the field “Is 00 at the end?” and choose from it.
Again, if the selection is by date, then break the dates into parts and select these parts.
These options are more bulky, but add speed for sure.

Answer 9

select field1, field2 from table
where mod(id, 60)=0
select field1, field2 from table
where mod(id, 360)=0
select field1, field2 from table
where mod(id, 1880)=0
etc…

Answer 10

To paraphrase more precisely ...
The value value is a temperature reading.
When building an hourly chart, 60 readings are required. From the database, we take one value for every minute of this hour. That is, if the time values ​​in the database records
look like this : :10 then we take only 22:19:00 22:20:00 What happened during this minute can be ignored.

Answer 11

I really liked about rrdtools. Found the link fairly quickly: Using rrdtool to monitor a network . Mega item!!! Did not know. Thanks for the tip.