GROUP BY hour in a date field?

From: Magnus Lassi <lassi_at_home.se>
Date: Sat, 03 Jul 1999 19:43:28 GMT
Message-ID: <377e672b.4849791@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 Sat Jul 03 1999 - 14:43:28 CDT