Unanswered: Extraction of records at specific time interval
I have a large volume of data that was recorded every second.
I want to extract data from the database every "x" number of seconds.
i.e. SELECT * FROM data WHERE time = (every 30 seconds);
The goal is to reduce the data set down to a number of records over time at an interval determined by the user.
What is your recommendation on how to accomplish this task?
I'm not sure if that would actually work in mysql , but that expression is the simplest way to explain it
the other option is to do some calculations (with php I assume) before the data is inserted, and determine if the time fits any time patterns. You could have columns to show if the time matches a 2 second, 3 second, 4 second, 5 second, 7 second or 9 second interval.
So if $time % 2 = 0 , you insert a '1' for the 2second column, if it doesnt you insert a 0 , same for all other intervals.
And then to get all data that matches the 30 second frequency...
SELECT * FROM data WHERE 2second = 1 AND 3second = 1 AND 5second = 1
2 * 3 * 5 = 30 , so any data matching those 3 columns must have a 30 second frequency.
With those 6 values you could make most combinations for all the frequencys you want to show.