Re: Help with to_timestamp

From: Adam Musch <ahmusch_at_gmail.com>
Date: Tue, 19 May 2009 11:57:01 -0500
Message-ID: <516d05a0905190957x17a42ad2w9e987d7fd9d9b253_at_mail.gmail.com>

The most straightforward way I can think of is to cast the minutes and seconds just to seconds:

SQL> variable l_waittime varchar2(5);
SQL> exec :l_waittime := '02:01';
PL/SQL procedure successfully completed. SQL>
SQL> select (to_number(substr(:l_waittime, 1, 2)) * 60) +   2 to_number(substr(:l_waittime, 4, 2)) as string_to_seconds   3 from dual;
STRING_TO_SECONDS

---

              121

SQL>
SQL> variable l_waitsecs number;
SQL> exec :l_waitsecs := 121;

PL/SQL procedure successfully completed. SQL>
SQL> select trim(to_char(trunc(:l_waitsecs/60), '00')) || ':' ||   2 trim(to_char(mod(:l_waitsecs, 60), '00')) as seconds_to_string   3 from dual;
SECONDS

---

02:01

On Tue, May 19, 2009 at 11:31 AM, Barbara Baker <barb.baker_at_gmail.com>wrote:

> 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 - 11:57:01 CDT