Re: TO_CHAR and TO_DATE question

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 - 15:13:52 CDT