I have a table that contains sensor data with a column
timestamp that holds the unix timestamp of the time the sensor measurement has been taken. Now I would like to
SELECT all measurements within a certain date/time range with a specific time step.
I figured the first part out myself like you can see in my posted code snippet below.
// With $date_start and $date_stop in the format: '2010-10-01 12:00:00' $result = mysqli_query($connection, "SELECT sensor_1 FROM sensor_table WHERE timestamp >= UNIX_TIMESTAMP($date_start) AND timestamp < UNIX_TIMESTAMP($date_stop) ORDER BY timestamp");
Now is there a convenient way in MySQL to include a time step size into the same
My table contains thousands of measurements over months with one measurement taken every 5 seconds.
Now let's say I would like to
SELECT measurements in between
2010-10-01 12:00:00 and
2010-10-02 12:00:00 but in this date/time range only
SELECT one measurement every 10 minutes? (as my table contains measurements taken every 5 seconds).
Any smart ideas how to solve this in a single query?
(also other ideas are very welcome :))