RE: Help with to_timestamp
- From: "Kenneth Naim" <kennaim@xxxxxxxxx>
- To: <barb.baker@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 May 2009 13:09:40 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
