Re: GROUP BY hour in a date field?

I don't know if this is exactly what you mean but try this:

If you want to group by date and hour use the following:

select trunc(measuredate,'HH24')
from....
where.....
group by trunc(measuredate, 'HH24')

This rounds down to the nearest hour and then groups the values:

If you want to group by the hour regardless of the date try this:

select to_char(trunc(measuredate, 'HH24'),'HH24') from....
where.....
group by to_char(trunc(measuredate, 'HH24'),'HH24')

Hope this helps.

Magnus Lassi <lassi_at_home.se> wrote in message news:377e672b.4849791_at_nntpserver.swip.net...
> hi gurus...
>
> I've been working on a problem which I haven't been able to solve. I
> have a table in a database with a bunch of fields I want to aggregate
> into another table. I want to group them by hour, ie all fields who
> took place at 1 PM, 2PM etc.. it's a DATE field with both the date and
> the timestamp in it.
>
> I don't know how to aggregate the DATE field in the table. I've tried
> both through TO_CHAR and converting it to SUBSTR.
>
> like the following:
> INSERT INTO dest60 VALUES
> (SELECT ne, rt, TO_CHAR(measuredate, 'YYYY-MM-DD HH24')
> FROM dest15)
> GROUP BY ne, rt, TO_CHAR(measuredate, 'YYYY-MM-DD HH24')
>
> I don't have the actual code with me but it's something like that. I
> have aggregate before but not anything like this problem. A general
> example on how to aggregate by hour (including the same day) would be
> very welcome.
>
> I would appreciate any insight to this problem.
>
> TIA,
> Magnus Lassi
>
Received on Tue Jul 06 1999 - 13:14:43 CDT