RE: Help with to_timestamp

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Tue, 19 May 2009 13:09:40 -0400
Message-ID: <015301c9d8a4$9b6651a0$d232f4e0$_at_com>

You can substr/instr the components out, convert them to numbers and then take the average.

select
avg(to_number(substr(cci.WAIT_TIME,1,instr(cci.WAIT_TIME,':',1,1)-1))+

    to_number(substr(cci.WAIT_TIME,instr(cci.WAIT_TIME,':',1,1)+1)/60) from OPS_MART.CIRCINFO cci

        where to_char(cci.CIRCDATE , 'YYMM') = to_char(cd.DASH_DATE -1 ,
'YYMM');
Depending on the data type and the data of circdate you might be able to get some performance improvements by tweaking the where clause as well.

Ken Naim

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barbara Baker
Sent: Tuesday, May 19, 2009 12:31 PM
To: ORACLE-L
Subject: Help with to_timestamp

Hi, all. Yep, I'm still alive and kicking.

I need to compute a month-to-date average for the column "average wait time". The time has been populated in the warehouse as character, and is in the format mm:ss

I want to do something along these lines

     to_char( (select avg(cci.WAIT_TIME) from OPS_MART.CIRCINFO cci
        where to_char(cci.CIRCDATE , 'YYMM') = to_char(cd.DASH_DATE -1 ,

'YYMM')
but to do this, I need to first convert from character into something useful.

I tried to_timestamp, which gives me

    select circdate, to_timestamp(wait_time, 'mi:ss') from circinfo

       18-MAY-09 01-MAY-09 12.01.52.000000000 AM Which I don't find particularly useful.

Any ideas??

Thanks for any help!!

OPS_MART:REPDB>describe circinfo

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 CIRCDATE                                  NOT NULL DATE
 WAIT_TIME                                          VARCHAR2(5)

OPS_MART:REPDB>l
  1* select circdate, wait_time from circinfo order by circdate 01-MAY-09 00:53
02-MAY-09 00:23
. . . . . .
17-MAY-09 00:48
18-MAY-09 01:52 -Barb Baker
Denver Newspaper Agency
Office: 303-954-5384

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 19 2009 - 12:09:40 CDT