Re: [sqlite] tricky date time problem

[Lloyd Thomas]

Dennis, syntax error somewhere.
'group by minute union  select 0, 0 where not exists (select * from 
event_data)'

Lloyd
- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, February 24, 2005 1:31 AM
Subject: Re: [sqlite] tricky date time problem


On Wed, 23 Feb 2005 23:12:41 -, Lloyd Thomas
<[EMAIL PROTECTED]> wrote:
Dennis,
Thanks for you help so far. I think it is easier for PHP to 
select
the MAX event. The problem I now have is if there is no records for an 
hour,
PHP will through up an error because MAX must have at least one record to
process, even if it is 0.

Lloyd,
You can force a zero record if there no events using a union like this:
select minutes.i as minute, count(*) as events
from
   (select (event_time / 60) % 60 as begin_minute,
   ((event_time + duration) / 60) % 60 as end_minute
   from event_data)
outer join integers60 as minutes
where
   case
   when begin_minute <= end_minute then
  begin_minute <= minutes.i and minutes.i <= end_minute
   else
 begin_minute <= minutes.i or minutes.i <= end_minute
   end
group by minute
union
select 0, 0 where not exists (select * from event_data)
This will give a single row with a count of zero if there are no
events, or the usual set of 60 rows if there are one or more events.
Dennis Cote 

Re: [sqlite] tricky date time problem

[Dennis Cote]

Lloyd,
A small improvement is to limit the number of rows used to test for event existence. This avoid  scanning the whole event_data table if there are many events.   

select minutes.i as minute, count(*) as events
from
   (select (event_time / 60) % 60 as begin_minute,  
   ((event_time + duration) / 60) % 60 as end_minute
   from event_data)
outer join integers60 as minutes
where
   case
   when begin_minute <= end_minute then
  begin_minute <= minutes.i and minutes.i <= end_minute
   else
 begin_minute <= minutes.i or minutes.i <= end_minute
   end
group by minute
union 
select 0, 0 where not exists (select * from event_data limit 1)

HTH
Dennis Cote

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

Dennis,
   Thanks for you help so far. I think it is easier for PHP to select 
the MAX event. The problem I now have is if there is no records for an hour, 
PHP will through up an error because MAX must have at least one record to 
process, even if it is 0.

Thanks again
- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: "sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, February 23, 2005 8:51 PM
Subject: Re: [sqlite] tricky date time problem


Dennis Cote wrote:
I though you wanted the minute with the most events. I added the outer 
select to show that that step can be done easily in the same query, 
rather than relying on PHP to extract this info from the full results for 
the hour.
But of course that portion of the query doesn't work the way I wanted it 
to. This is a common problem with all SQL aggregate functions. They 
generate tables with their won rows, they don't return rows fromt the 
table they are summarizing.

To do what I want you need to build a temp table or recalculate the event 
count table twice (because SQLite doesn't support named subqueries). You 
then need to find all the minutes with counts that match the maximum value 
because there could be more that one minute with the same count value.

Using a temp table:
create temp table t as
select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
else
  begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute

select minute, events
from t
where events = (select max(events) from t)
order by minute
drop table t

Or using repeated sub-queries (basically replace each instance of t in the 
above query with the entire query used to build the event count table):

select minute, events from (
select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
else
  begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute
) where events = (select max(events) from (
select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
else
  begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute
)) order by minute

The second has the advantage that it is a single (complex) query, but 
neither is really pretty, so perhaps using PHP isn't such a bad idea.

P.S. Richard, this is another example of where named subqueies and the 
WITH clause allow more efficient SQL to be written. This could be stated 
as below if this feature was supported by SQLite.

with
   event_counts as (
   select minutes.i as minute, count(*) as events
   from
   (select (event_time / 60) % 60 as begin_minute, ((event_time + 
duration) / 60) % 60 as end_minute
   from event_data)
   outer join integers60 as minutes
   where
   case
   when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
   else
   begin_minute <= minutes.i or minutes.i <= end_minute
   end
   group by minute )
select minute, events
from event_counts
where events = (select max(events) from event_counts)
order by minute

Re: [sqlite] tricky date time problem

[Dennis Cote]

Dennis Cote wrote:
I though you wanted the minute with the most events. I added the outer 
select to show that that step can be done easily in the same query, 
rather than relying on PHP to extract this info from the full results 
for the hour.
But of course that portion of the query doesn't work the way I wanted it 
to. This is a common problem with all SQL aggregate functions. They 
generate tables with their won rows, they don't return rows fromt the 
table they are summarizing.

To do what I want you need to build a temp table or recalculate the 
event count table twice (because SQLite doesn't support named 
subqueries). You then need to find all the minutes with counts that 
match the maximum value because there could be more that one minute with 
the same count value.

Using a temp table:
create temp table t as
select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
else
  begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute

select minute, events
from t
where events = (select max(events) from t)
order by minute
drop table t

Or using repeated sub-queries (basically replace each instance of t in 
the above query with the entire query used to build the event count table):

select minute, events from (
select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
else
  begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute
) where events = (select max(events) from (
select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
else
  begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute
)) order by minute

The second has the advantage that it is a single (complex) query, but 
neither is really pretty, so perhaps using PHP isn't such a bad idea.

P.S. Richard, this is another example of where named subqueies and the 
WITH clause allow more efficient SQL to be written. This could be stated 
as below if this feature was supported by SQLite.

with
   event_counts as (
   select minutes.i as minute, count(*) as events
   from
   (select (event_time / 60) % 60 as begin_minute, 
   ((event_time + duration) / 60) % 60 as end_minute
   from event_data)
   outer join integers60 as minutes
   where
   case
   when begin_minute <= end_minute then
   begin_minute <= minutes.i and minutes.i <= end_minute
   else
   begin_minute <= minutes.i or minutes.i <= end_minute
   end
   group by minute )
select minute, events
from event_counts
where events = (select max(events) from event_counts)
order by minute

 

Re: [sqlite] tricky date time problem

[Dennis Cote]

Lloyd Thomas wrote:
Revisted your query. I was being a bit lazy. changed 'select minute, 
max(events)' to 'select minute, events' and added  'order by minute' 
to give me what I needed.
Thanks
Lloyd,
If you want the whole table simply remove the outer select and add the 
order by clause as below.

 select minutes.i as minute, count(*) as events
 from
 (select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
 from event_data)
 outer join integers60 as minutes
 where
 case
 when begin_minute <= end_minute then
begin_minute <= minutes.i and minutes.i <= end_minute
 else
   begin_minute <= minutes.i or minutes.i <= end_minute
 end
 group by minute
 order by minute

I though you wanted the minute with the most events. I added the outer 
select to show that that step can be done easily in the same query, 
rather than relying on PHP to extract this info from the full results 
for the hour.

Dennis Cote

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

Dennis,
   Revisted your query. I was being a bit lazy. changed 'select minute, 
max(events)' to 'select minute, events' and added  'order by minute' to give 
me what I needed.
Thanks

- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: "sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, February 23, 2005 5:41 PM
Subject: Re: [sqlite] tricky date time problem


Lloyd,
I messed up the math for the end minute calculation. :-[
The correct query is given below. Adding  59 was intended to round the 
result of an integer division (which drops the remainder), but I'm 
actually keeping the reminader and throwing away the quotient so it was 
simply wrong.

select minute, max(events)
from
  (select minutes.i as minute, count(*) as events
  from
  (select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
  from event_data)
  outer join integers60 as minutes
  where
  case
  when begin_minute <= end_minute then
 begin_minute <= minutes.i and minutes.i <= end_minute
  else
begin_minute <= minutes.i or minutes.i <= end_minute
  end
  group by minute)

HTH
Dennis Cote

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

Now I am confused.
Jay, I tried your solution but I still end up with one result
0|14
I need to now the result for each minute(even if null) during the hour.
Dennis, I tried yours and ended up with much the same thing
0|4
this is the contents of the table between 2004-04-07 10:00:00 and 2004-04-07 
10:59:59
call_id|event_time|duration
7|1081332060|129
8|1081332540|208
10|1081332900|180
11|108180|44
12|1081333500|27
13|108180|229
14|1081334280|0
15|1081334280|0
16|1081335120|11
17|1081335120|0
18|1081335360|40
19|1081335420|46
20|1081334940|719
23|1081334460|1802

Maybe this data may indicate where I am going wrong.
Lloud
- Original Message - 
From: "Jay" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 23, 2005 5:39 PM
Subject: Re: [sqlite] tricky date time problem


--- Lloyd Thomas <[EMAIL PROTECTED]> wrote:
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)
The modulo concept is simple:
unix time = number of seconds (since 1970 if I remember correctly,
but that's not important)
If you divide the start time, ie. the number of seconds, by 60 and
keep the reminder it tells you the n-th second during each minute
when the event occurred. Which was exactly what I thought you were
looking for.
for example:
event 1 happens at 100 seconds.
event 2 happens at 112 seconds.
event 3 happens at 123 seconds.
event 4 happens at 183 seconds.
100 % 60 = 40  ( 1 minute 40 seconds )
112 % 60 = 52  ( 1 minute 52 seconds )
123 % 60 =  3  ( 2 minutes 3 seconds )
183 % 60 =  3  ( 3 minutes 3 seconds )
select hour_start % 60 as seconds, count(*) as count
from your_table
group by seconds
gives this:
secondscount
   
3   2
40  1
52  1
I hope that's what you wanted!
__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

Re: [sqlite] tricky date time problem

[Dennis Cote]

Lloyd,
I messed up the math for the end minute calculation. :-[ 

The correct query is given below. Adding  59 was intended to round the 
result of an integer division (which drops the remainder), but I'm 
actually keeping the reminader and throwing away the quotient so it was 
simply wrong.

select minute, max(events)
from
  (select minutes.i as minute, count(*) as events
  from
  (select (event_time / 60) % 60 as begin_minute,  ((event_time + 
duration) / 60) % 60 as end_minute
  from event_data)
  outer join integers60 as minutes
  where
  case
  when begin_minute <= end_minute then
 begin_minute <= minutes.i and minutes.i <= end_minute
  else
begin_minute <= minutes.i or minutes.i <= end_minute
  end
  group by minute)

HTH
Dennis Cote

Re: [sqlite] tricky date time problem

[Jay]

--- Lloyd Thomas <[EMAIL PROTECTED]> wrote:

> 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)

The modulo concept is simple:

unix time = number of seconds (since 1970 if I remember correctly,
but that's not important)

If you divide the start time, ie. the number of seconds, by 60 and
keep the reminder it tells you the n-th second during each minute
when the event occurred. Which was exactly what I thought you were
looking for.

for example:

event 1 happens at 100 seconds.
event 2 happens at 112 seconds.
event 3 happens at 123 seconds.
event 4 happens at 183 seconds.

100 % 60 = 40  ( 1 minute 40 seconds )
112 % 60 = 52  ( 1 minute 52 seconds )
123 % 60 =  3  ( 2 minutes 3 seconds )
183 % 60 =  3  ( 3 minutes 3 seconds )

select hour_start % 60 as seconds, count(*) as count
 from your_table
 group by seconds

gives this: 

secondscount
   
3   2
40  1
52  1

I hope that's what you wanted!

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] tricky date time problem

[Uriel_Carrasquilla]

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

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

I have two tables the event table which holds the data and a table which has 
an entry for each minute

CREATE TABLE event_data (
 call_id INTEGER PRIMARY KEY,
 desc varchar(32) NOT NULL default '',
 event_time datetime default NULL,
 stamptime integer default NULL,
 duration integer default NULL
);
'stamptime' is the unix time (Number of seconds
since 1970) representation of  'event_time'
'duration' is the duration of the event in seconds
CREATE TABLE integers60 (
 i INTEGER
);
So I was hoping to us the query the integers table and use the results 'i' 
as a datatime modifier in the event_data table. Not much luck though.

- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Wednesday, February 23, 2005 12:26 PM
Subject: Re: [sqlite] tricky date time problem


On Mon, 2005-02-21 at 21:49 +, Lloyd Thomas wrote:
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.

How are you storing time values?  Julian day number/  Number of seconds
since 1970?  IS09601?  And what is the schema for the table you are
querying?
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] tricky date time problem

[D. Richard Hipp]

On Mon, 2005-02-21 at 21:49 +, Lloyd Thomas wrote:
> 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.
> 

How are you storing time values?  Julian day number/  Number of seconds
since 1970?  IS09601?  And what is the schema for the table you are
querying?
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

Still no joy. I have tried to use the % operator but I do not get the 
expected results. Can some one point me in the right direction?
I have simplified my query to test

Select 3600 %60 as seconds, count (event_id) from event_data
WHERE event_time >= 1081331940 and event_time-duration <= 1081335540
The result I get is
0|15
I was expecting 60 result rows
any Ideas? Is this a version 3 operator only?
- Original Message - 
From: "Lloyd Thomas" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, February 22, 2005 11:41 PM
Subject: Re: [sqlite] tricky date time problem


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

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

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 

Re: [sqlite] tricky date time problem

[Jay]

--- "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 

Re: [sqlite] tricky date time problem

[Jay]

//--
// Calculate Day name from datetime value
//--
void ModFunc( sqlite3_context *context, int argc, sqlite3_value **argv
)
  {
// retrieve first parameter
long l = sqlite3_value_int64( argv[0] );
// calculate modulo 60 (to return 0 - 59)
l %= 60;
// return as a string, or change to a number if you like
sqlite3_result_text( context, p, 3, free );
  }

main()
  {
// connect to database
rc = sqlite3_open( "somedata.db",  );
if ( rc )
  throw exception( string("Can't open database: ") +
sqlite3_errmsg( db ) );

// define function used in select
rc = sqlite3_create_function( db, "ModSec", 1, SQLITE_UTF8,
NULL, ModFunc, NULL, NULL );
if ( rc )
  {
// log the details here for later debugging
sqlite3_close( db );
throw exception( string("Can't define function: ") +
sqlite3_errmsg( db ) );
  }

// Assuming you have a table named 'x'
// and a field named 'when' that has a unix style
// long integer date. The 'group by' will return one
// row for each event that happened N seconds into each minute.
string sql = "SELECT ModSec(when), COUNT(*) FROM x GROUP BY
ModSec(when)";
rc = sqlite3_exec( db, sql.c_str(), PeopleCallback, 0, 
);
if ( rc != SQLITE_OK )
  {
sqlite3_close( db );
throw ConException( string("SQL failed: '") + sql +
string("'") + zErrMsg );
  }

sqlite3_close( db );
 
  }

--- Lloyd Thomas <> wrote:

> Jay,
> ---
> |You could select and group by the modulo of the seconds of each|
> |date and get your nice groupings very simply. If you can write a   
> |
> |user defined function in whatever language you're using 
>|
> |you might try that. 
> |
> ---
> How would you do that in C?
> 
> - Original Message - 
> From: "Jay" <[EMAIL PROTECTED]>
> To: <sqlite-users@sqlite.org>; <[EMAIL PROTECTED]>
> Sent: Tuesday, February 22, 2005 2:00 AM
> Subject: Re: [sqlite] tricky date time problem
> 
> 
> >
> > --- Lloyd Thomas <[EMAIL PROTECTED]> wrote:
> >
> >> 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
> >
> > It's too bad Sqlite doesn't have the modulo operator, you could
> > select and group by the modulo of the seconds of each date and get
> > your nice groupings very simply. If you can write a user defined
> > function in whatever language you're using you might try that.
> >
> >
> >
> > __
> > Do you Yahoo!?
> > The all-new My Yahoo! - What will yours do?
> > http://my.yahoo.com 
> 
> 


=

-

"Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of 
the ancient tomb of the petrified pharaoh, he vowed there would be no curse on 
him like on that other Lord, unless you count his marriage to Lady Tarlington 
who, when the lost treasure was found, will be dumped faster than that basket 
in the bulrushes."
  Melissa Rhodes
-

The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's 
Call
http://www.lulu.com/content/77264



__ 
Do you Yahoo!? 
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

Jay,
---
|You could select and group by the modulo of the seconds of each|
|date and get your nice groupings very simply. If you can write a|
|user defined function in whatever language you're using |
|you might try that. 
|
---
   How would you do that in C?

- Original Message - 
From: "Jay" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 22, 2005 2:00 AM
Subject: Re: [sqlite] tricky date time problem


--- Lloyd Thomas <[EMAIL PROTECTED]> wrote:
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
It's too bad Sqlite doesn't have the modulo operator, you could
select and group by the modulo of the seconds of each date and get
your nice groupings very simply. If you can write a user defined
function in whatever language you're using you might try that.

__
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

Re: [sqlite] tricky date time problem

[Jay]

--- Lloyd Thomas <[EMAIL PROTECTED]> wrote:

> 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

It's too bad Sqlite doesn't have the modulo operator, you could
select and group by the modulo of the seconds of each date and get
your nice groupings very simply. If you can write a user defined
function in whatever language you're using you might try that.



__ 
Do you Yahoo!? 
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

Re: [sqlite] tricky date time problem

[Roger Binns]

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

Re: [sqlite] tricky date time problem

[Lloyd Thomas]

I am trying to simplify my query but seem to be failing at the first hurdle.
While if I do
select * from event_id from eveny_data where event_time between '2004-04-07 
10:00:00' and '2004-04-07 10:59:59';
returns 15 rows

select * from event_id from eveny_data where event_time between 
datetime('2004-04-07 10:00:00','+1 minutes') and datetime ('2004-04-07 
10:59:59','+1 minutes');
returns nothing.
Am I using the right datetime modifiers?

- Original Message - 
From: "Lloyd Thomas" <[EMAIL PROTECTED]>
To: 
Sent: Monday, February 21, 2005 9:49 PM
Subject: [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.

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