Solved: Help with to_timestamp

From: Barbara Baker <barb.baker_at_gmail.com>
Date: Tue, 19 May 2009 14:35:32 -0600
Message-ID: <47a6f72b0905191335h7101fb14kf2049fd1ed1c440e_at_mail.gmail.com>

Thanks to everyone for their help.
Because of the odd way this query was constructed and the format they required, I used pretty much what Ken recommended. whew!! This one is ugly.
Thanks again!!

to_char(
 to_date(
  ((select avg ((to_number(substr(cci.WAIT_TIME,1,instr(cci.WAIT_TIME,':',1,1)-1))*60)

+to_number(substr(cci.WAIT_TIME,instr(cci.WAIT_TIME,':',1,1)+1))) from circinfo cci

       where to_char(cci.CIRCDATE , 'YYMM') = to_char(cd.DASH_DATE -1 , 'YYMM')
                and     cci.CIRCDATE <= cd.DASH_DATE -1)) ,

'SSSSS'), 'MI:SS') c2,

---




On Tue, May 19, 2009 at 11:09 AM, Kenneth Naim <kennaim_at_gmail.com> wrote:


> 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 - 15:35:32 CDT