select minute avg in one minute increments

Question

I am trying to come up with a way to do the following in a single SQL.

I would like to select all times between 09:00 and 17:30 in one minute increments without using a for loop. Is there a way to do it?

select avg(price), Date(timecolumn)
from table 
where Time(timeColumn) >= '09:00:00'
and Time(timeColumn) <= '09:01:00'

Answer 1

You have to group by date, hour, and minute.

SELECT AVG(price), DATE(timecolumn), HOUR(timecolumn), MINUTE(timecolumn)
FROM `table` 
WHERE TIME(timeColumn) >= '09:00:00' AND TIME(timeColumn) <= '17:01:00'
GROUP BY DATE(timecolumn), HOUR(timecolumn), MINUTE(timecolumn)

Answer 2

I'm assuming the big hurdle here is you want to keep (and return) rows for the zeroes (or NULLs) - that is, one-minute increments with no rows in your table table.

You might want to consider adding a table to your database with times in it. Something like:

FullTime TIME,
Hour INT,
Minute INT,
Second INT

86400 rows gets you precision down to the second and you can put indexes on it.

Then you can just join with this table:

SELECT AVG(Price), DATE(timeColumn)
FROM times
LEFT OUTER JOIN myTable ON times.FullTime = myTable.timeColumn
WHERE times.timeColumn BETWEEN '09:00:00' AND '17:30:00'
AND times.Seconds = 0
GROUP BY times.FullTime