Time interval SQL query with MySQL

Question

I've got a table in a database that stores log data by time. For one day there can be a million rows in the db. The times are not at any regular interval. It has several indexes, including the time. What I want to do is build a query that will return a set of rows with one row per time interval. For example, I could do a query to return 1 row every 15 minutes for a day. This would return 24*60=96 rows. Each row returned would actually be the nearest row in the db prior to the interval requested (since the data in the database will not equal the requested interval). I am at a loss for how to do it. I can't just query all rows for a particular set of indexes and time interval, as it would load more than a gigabyte of data into memory, which is too slow. Is there any efficient way to do this using SQL. I'm using a MySQL database. I would be open to changing the table indexes/etc... ``` TIME 11:58 12:03 12:07 12:09 12:22 12:27 12:33 12:38 12:43 12:49 12:55 ``` If I wanted to query this fo…

Accepted Answer

So, I had thought something like:

```
SELECT 
  MIN(timeValue)
FROM e
GROUP BY (to_seconds(timeValue) - (to_seconds(timeValue) % (60 * 5)))
```

..would do it for you, but this only returns the MIN(timeValue) over the whole table. It works if the seconds rounded to the nearest 5 min is in its own col.

See SQL Fiddle

Edit per Andiry, this works: ( http://sqlfiddle.com/#!2/bb870/6 )

```
SELECT MIN(t)
FROM e
GROUP BY to_seconds(t) DIV (60 * 5)
```

But this just gives one row: ( http://sqlfiddle.com/#!2/bb870/7 )

```
SELECT MIN(t)
FROM e
GROUP BY to_seconds(t) - (to_seconds(t) % (60 * 5))
```

Anyone know why?

Time interval SQL query with MySQL

Solution

Problem

Related problems