[sqlite] tricky date time problem

[Lloyd Thomas]

I have a query which calculates the number of events during an hour by the 
minute.  It needs to work out which minute has the most events and the 
average events during that hour. So it should return an array of  60 results 
for an hour where I can use the MAX() feature in php to find the peak 
minute.

currently the query looks like this:
SELECT event_id, intdur FROM event_data where event_time <= 
datetime('2004-04-07 00:00:00', '+ i minutes')
AND datetime('2004-04-07 00:00:00', '+ i minutes', '- intdur seconds') <= 
event_time";
but it takes an age to work as I have to run the query 60 times for each 
hour

I was thinking of creating a seperate table with a row for each minute and 
then run a query against that table using the minute integer as part of a 
sub-select against the event table. I have tried but can't get it to work.

so far I get an error: only a single result allowed for a SELECT that is 
part of  an expression
----------------------------------------------
SELECT count(i) from integers60 WHERE (SELECT event_id, intdur from 
event_data where event_time <= datetime('2004-04-07 10:00:00', '+ i 
minutes') AND datetime('2004-04-07 10:00:00', '+ i minutes', '+ intdur) <= 
event_time)

Can some one help?
Lloyd