Re: TO_CHAR and TO_DATE question

From: Mark van Kerkwyk <kerkwyk_at_comtech.com.au>
Date: Wed, 09 Jun 1999 07:34:11 +1000
Message-ID: <375D8C53.3D5344CD@comtech.com.au>

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 ??

>>

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 :-)
Received on Tue Jun 08 1999 - 16:34:11 CDT