this looks more promising (The "%" operator gives you remainder after division). Still not sure how I could apply it to start and end unix times.
The columns I have are :-
hour start = start time of query for event (unix time)
hour end = end time of query for event (unix time)
event time = start time of event(unix time)
duration = duration of event(seconds)
So I need to know the amount of simultaneous events durring each minute.
I can use 'SELECT(hour_end - hour_start)%60 as seconds, count(event_id) AS event_num' to give me my minute intervals
To make sure that the events are within the events
'WHERE event_time >= hour_start AND event_time-duration <= hour end'
then compare wether seconds fall in between the event start and end
'AND seconds BETWEEN (event_time-hour_start)%60 AND (event_time-hour_start+duration)%60'
finally
'GROUP' by seconds'
Would this be the best way to do this query?
I apologise for my bad SQL syntax in advance as I am not great at it, a symptom of knowing a little of a lot and lot of little.
Lloyd
----- Original Message ----- From: "Jay" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, February 22, 2005 2:47 PM
Subject: Re: [sqlite] tricky date time problem
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> It's too bad Sqlite doesn't have the modulo operator,
The "%" operator gives you remainder after division, which is very close to being a modulo operator.
Thanks! I went looking for the page in the documentation about expressions to see what math sqlite supported. I couldn't find it and assumed it didn't have all the math functions.
Lloyd: Since it does have this operator please disregard my example showing how to create a user defined function.
You can do what you want by using :
select tm % 60 as second, count(*) from your_table Group by second
The tm field must contain the time in seconds of your event. You'll get up to 60 result rows with 0 - 59.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
