| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: TO_CHAR and TO_DATE question
A copy of this was sent to Mark van Kerkwyk <kerkwyk_at_comtech.com.au>
(if that email address didn't require changing)
On Wed, 09 Jun 1999 07:34:11 +1000, you wrote:
>Hi Steve, thanks for your reply.
>The only reason why I was using to TO_CHAR was for the purposes of getting the
>Hour,Day and Month components for grouping purposes. Is there a way to group by
>Hour,Day,Month without converting with TO_CHAR ??
>
use TRUNC() instead.
trunc( date_column, 'hh24' ) -> trunc'ed to the hour. trunc( date_column, 'dd' ) -> trunc'ed to the day. trunc( date_column, 'mm' ) -> trunc'ed to the month.
so:
>> > insert into coldatadaily (ipaddr,instance,varid,collecttime,floatvalue)
>> > select
>> > ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy'),round(avg(floatvalue))
>> >
>> > from coldata group by
>> > ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy');
would be
>> > insert into coldatadaily (ipaddr,instance,varid,collecttime,floatvalue)
>> > select
>> > ipaddr,instance,varid, trunc( collecttime, 'dd' ),round(avg(floatvalue))
>> >
>> > from coldata group by
>> > ipaddr,instance,varid,trunce( collecttime, 'dd') ;
and so on.
>>>
>Why not just use views of the existing data.
><<
>
>I'm using Oracle 7.x , and am looking for performance as this data will be queried
>real time for graphical reports, I wanted to summarise the data for speed purposes,
>views were far too slow.
>
>
>Mark :-)
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>Steven Franklin wrote:
>
>> Why not just store the dates as DATE types instead of trying to convert them into
>> strings? Just use TO_CHAR() for formating reports. Also, why move the data that
>> is already in the database? Why not just use views of the existing data.
>>
>> Mark van Kerkwyk wrote:
>>
>> > Hi,
>> > I am trying to create some summary tables for lots of collected
>> > data.
>> > For many devices , I collect a number of stats every 5 mins.
>> > I have created Hourly,Daily and Monthly tables
>> > Source table is COLDATA
>> > Hourly -> COLDATAHOURLY
>> > Daily -> COLDATADAILY
>> > Monthly->COLDATAMONTHLY
>> >
>> > I have made the following scripts to transfer the data from the master
>> > table into the summary tables.
>> >
>> > Hourly Script
>> >
>> > insert into coldatahourly (ipaddr,instance,varid,collecttime,floatvalue)
>> > select
>> > ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy
>> > HH24'),round(avg(floatvalue))
>> > from coldata group by
>> > ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy HH24');
>> >
>> > Daily Script
>> >
>> > insert into coldatadaily (ipaddr,instance,varid,collecttime,floatvalue)
>> > select
>> > ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy'),round(avg(floatvalue))
>> >
>> > from coldata group by
>> > ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy');
>> >
>> > Monthly Script
>> >
>> > insert into coldatamonthly
>> > (ipaddr,instance,varid,collecttime,floatvalue) select
>> > ipaddr,instance,varid,to_char(collecttime,'Mon-yy'),round(avg(floatvalue))
>> >
>> > from coldata group by
>> > ipaddr,instance,varid,to_char(collecttime,'Mon-yy');
>> >
>> > This works fine until I get to the Monthly one, where the sort order
>> > will have to be yyyy-Mon instead of Mon-yyyy, otherwise it won't group
>> > properly.
>> > The problem I have is that I am unable to get a TO_DATE working on the
>> > insert section of the statement, no matter what I do, I get an error
>> > back telling me that there is a comma missing, I have tried every
>> > combination of TO_DATE and keep getting the same error, if I can't
>> > format the date going in the insert, the statement fails telling me that
>> > I am trying to insert an invalid date.
>> >
>> > The column in question here is named COLLECTTIME (DATE type), all other
>> > columns are OK.
>> >
>> > Can anyone give me an example of how I might better create and run these
>> > scripts and use the TO_DATE function to format the data coming into the
>> > COLLECTTIME field
>> >
>> > Your help would be much appreciated.
>> >
>> > Mark :-)
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |