Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Group by half-hour intervals?
>Have used other RDB's but new to Oracle and don't have docs yet.
>
>Any clue as to how I might query a table containing a timestamp
>field and perform statistical functions on rows grouped by half
>hour intervals?
>
>TIA
>
>Roger Tomas
>AG Communication Systems
>
>
You just need a do devise an equation that provides this information...assuming that 3:30pm on one day is different from 3:30pm on another day....
Others might suggest using the "MI' format, but I think the best way is to use the Julian number (which will return an integer number of days since some time in the distant past, and a decimal part that is how much of the day has gone by - Noon would be .5 etc). 1/2 Hour is 1/48th of the day.
Something like
To convert the Julian date to the 'half hour number' multiply by 48 (getting
the number of 1/2 hours intervals since day 0) and truncate.
trunc(48*number(to_char(my_date,'J')))
Use that in the group by, or add another column to the table with this value in it and use that column in the group bys.
(incidently, you still have the full date, to the half hour, available for processing/labels/etc)
You can also use truncate on date fields, and truncate to days, hours, etc, but I don't think there is an option for half hours.
Dan Hekimian-Williams Received on Wed Jul 01 1998 - 20:43:49 CDT