Help with to_timestamp

From: Barbara Baker <barb.baker_at_gmail.com>
Date: Tue, 19 May 2009 10:31:20 -0600
Message-ID: <47a6f72b0905190931t3cc33f27i8f2101c94421d3c4_at_mail.gmail.com>

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 Received on Tue May 19 2009 - 11:31:20 CDT