Re: Group by half-hour intervals?
In article <35A3987A.E3FF10A5@agcs.com>, Roger Tomas <tomasr@agcs.com> wrote:
>Any clue as to how I might query a table containing a datetime
>field and perform statistical functions on rows grouped by half
>hour intervals?
>
>TIA!
>
>Roger Tomas
>AG Communication Systems
>
>
{ Here's one way....
MY_TABLE has 1515 records such that timestamp1 > "1998-07-01 00:00:00"
If I create a temp table t1 and populate it with the 48 half-hour
periods that make up a day, then I can join MY_TABLE with t1
to see how those 1515 records fall into half-hour periods: I got
(expression) start finish (count(*))
07/01/1998 12:30:00 12:59:59 159
07/01/1998 13:00:00 13:29:59 716
07/03/1998 13:00:00 13:29:59 457
07/06/1998 12:00:00 12:29:59 71
07/07/1998 12:30:00 12:59:59 112
5 row(s) retrieved.
from the following SQL. Perhaps something like this can be adapted
to your purposes?
- Paul (Adventurer & romantic trapped in the body of a
programmer/analyst, but decidedly not a spokesman) }
create temp table t1 ( per_num smallint,
start datetime hour to second,
finish datetime hour to second ) with no log ;
insert into t1 values ( 1, "00:00:00", "00:29:59" ) ;
insert into t1 values ( 2, "00:30:00", "00:59:59" ) ;
insert into t1 values ( 3, "01:00:00", "01:29:59" ) ;
insert into t1 values ( 4, "01:30:00", "01:59:59" ) ;
insert into t1 values ( 5, "02:00:00", "02:29:59" ) ;
insert into t1 values ( 6, "02:30:00", "02:59:59" ) ;
insert into t1 values ( 7, "03:00:00", "03:29:59" ) ;
insert into t1 values ( 8, "03:30:00", "03:59:59" ) ;
insert into t1 values ( 9, "04:00:00", "04:29:59" ) ;
insert into t1 values ( 10, "04:30:00", "04:59:59" ) ;
insert into t1 values ( 11, "05:00:00", "05:29:59" ) ;
insert into t1 values ( 12, "05:30:00", "05:59:59" ) ;
insert into t1 values ( 13, "06:00:00", "06:29:59" ) ;
insert into t1 values ( 14, "06:30:00", "06:59:59" ) ;
insert into t1 values ( 15, "07:00:00", "07:29:59" ) ;
insert into t1 values ( 16, "07:30:00", "07:59:59" ) ;
insert into t1 values ( 17, "08:00:00", "08:29:59" ) ;
insert into t1 values ( 18, "08:30:00", "08:59:59" ) ;
insert into t1 values ( 19, "09:00:00", "09:29:59" ) ;
insert into t1 values ( 20, "09:30:00", "09:59:59" ) ;
insert into t1 values ( 21, "10:00:00", "10:29:59" ) ;
insert into t1 values ( 22, "10:30:00", "10:59:59" ) ;
insert into t1 values ( 23, "11:00:00", "11:29:59" ) ;
insert into t1 values ( 24, "11:30:00", "11:59:59" ) ;
insert into t1 values ( 25, "12:00:00", "12:29:59" ) ;
insert into t1 values ( 26, "12:30:00", "12:59:59" ) ;
insert into t1 values ( 27, "13:00:00", "13:29:59" ) ;
insert into t1 values ( 28, "13:30:00", "13:59:59" ) ;
insert into t1 values ( 29, "14:00:00", "14:29:59" ) ;
insert into t1 values ( 30, "14:30:00", "14:59:59" ) ;
insert into t1 values ( 31, "15:00:00", "15:29:59" ) ;
insert into t1 values ( 32, "15:30:00", "15:59:59" ) ;
insert into t1 values ( 33, "16:00:00", "16:29:59" ) ;
insert into t1 values ( 34, "16:30:00", "16:59:59" ) ;
insert into t1 values ( 35, "17:00:00", "17:29:59" ) ;
insert into t1 values ( 36, "17:30:00", "17:59:59" ) ;
insert into t1 values ( 37, "18:00:00", "18:29:59" ) ;
insert into t1 values ( 38, "18:30:00", "18:59:59" ) ;
insert into t1 values ( 39, "19:00:00", "19:29:59" ) ;
insert into t1 values ( 40, "19:30:00", "19:59:59" ) ;
insert into t1 values ( 41, "20:00:00", "20:29:59" ) ;
insert into t1 values ( 42, "20:30:00", "20:59:59" ) ;
insert into t1 values ( 43, "21:00:00", "21:29:59" ) ;
insert into t1 values ( 44, "21:30:00", "21:59:59" ) ;
insert into t1 values ( 45, "22:00:00", "22:29:59" ) ;
insert into t1 values ( 46, "22:30:00", "22:59:59" ) ;
insert into t1 values ( 47, "23:00:00", "23:29:59" ) ;
insert into t1 values ( 48, "23:30:00", "23:59:59" ) ;
select date(MY_TABLE.timestamp1), t1.start, t1.finish, count(*)
from MY_TABLE, t1
where MY_TABLE.timestamp1 > "1998-07-01 00:00:00"
and extend ( timestamp1, hour to second ) between t1.start and t1.finish
group by 1,2,3
order by 1,2,3
{ As you can see, I don't actually do anything with per_num but it might
be a more convenient way of specifiying a half-hour period than quoting
the start and finish times. }