Re: [sqlite] tricky date time problem

[Lloyd Thomas]

thanks for your reply. I think writing  a new function is beyond me at the 
moment as I have no knowledge of C. I have found the problem with my 
datetime modifiers.

I am trying various queries but still getting no results.
I think the best way to attack this is using the my minute integer table
---
|  i |
---
| 0 |
| 1 |
|..  |
|59|
and then do a LEFT JOIN so all the minutes are returned
but I still get no results.
SELECT i, call_time FROM integers60
LEFT OUTER JOIN call_data
ON call_time
BETWEEN datetime('2004-04-07 10:00','+i minutes')
AND datetime('2004-04-07 10:00','+i+1 minutes')
ORDER BY i  , call_time;
May it be something to do with the 'i' not being seen as an integer for the 
datetime modifier?

----- Original Message ----- 
From: "Roger Binns" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, February 22, 2005 12:02 AM
Subject: Re: [sqlite] tricky date time problem


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.
You may find it easier to write an aggregate function and use that.
 http://www.sqlite.org/capi3ref.html#sqlite3_create_function
If you aren't using the SQLite C api directly, then the wrapper
you use may provide this ability.
Roger