Lloyd:
Let me call a variable $seconds and perform your mod 60 and assign the
results to $newSeconds:
"$newSeconds = $seconds % 60". If you were to look at $newSeconds in the
HH:MM;SS, you will find all your SS values were set to zero for each
record.
I am assuming that your SQLite Table contains a column with seconds
representing a Unix Epoch (or any other similar scheme but the point is
that the column contain only seconds).
I normally use this approach to SYNC time to, let's say, every 10 minutes
($newSeconds = $seconds % 600). I can now proceed to summarize data for a
10 minute interval. In your case, all you really want to drop is the
seconds, so you should be able to retrieve your information and drop the
seconds. Nothing fancy but effective.
I hope this helps.
Regards,
[EMAIL PROTECTED]
NCCI
Boca Raton, Florida
561.893.2415
greetings / avec mes meilleures salutations / Cordialmente
mit freundlichen Grüßen / Med vänlig hälsning
"Lloyd Thomas"
<[EMAIL PROTECTED] To:
<sqlite-users@sqlite.org>
n.plus.com> cc:
Subject: Re: [sqlite] tricky
date time problem
02/22/2005 06:41 PM
Please respond to
sqlite-users
Thanks Jay/DRH,
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
