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|1081333380|44
12|1081333500|27
13|1081333380|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:
seconds count -------- -------- 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
