Thread: FW: Re: Part 1 of several - Converting a varchar to an interval

-------------- Forwarded Message: --------------
From: aklaver@comcast.net (Adrian Klaver)
To: Shawn <postgres@xmtservices.net>
Subject: Re: [SQL] Part 1 of several - Converting a varchar to an interval
Date: Wed, 26 Mar 2008 16:16:58 +0000
> 
> 
> 
> 
>  -------------- Original message ----------------------
> From: Shawn <postgres@xmtservices.net>
> > Hello,
> > 
> > I have several large tables, over 100 million records each.  One of the fields 
> > is callee 'duration'.  It is a varchar that contains what is essentially an 
> > integer that is the duration of an event in milleseconds.  Could someone tell 
> > me a simple way to convert a value such as 134987 stored in a varchar into an 
> > interval?
> > 
> > This will dovetail with my next question. 
> > 
> > Thanks for all the help both now and previous.
> > 
> > Shawn
> > 
> 
select ('134987'::int/1000.00) * interval ' 1 second' ;   ?column?-------------- 00:02:14.987(1 row)


Forgot to Reply All.
--
Adrian Klaver
aklaver@comcast.net

aklaver@comcast.net (Adrian Klaver) writes:
> select ('134987'::int/1000.00) * interval ' 1 second' ;
>     ?column?
>  --------------
>   00:02:14.987
>  (1 row)

Or even easier:

regression=# select 134987 * interval '1 msec';  ?column?   
--------------00:02:14.987
(1 row)

        regards, tom lane

-------------- Original message ----------------------
From: Tom Lane <tgl@sss.pgh.pa.us>
> aklaver@comcast.net (Adrian Klaver) writes:
> > select ('134987'::int/1000.00) * interval ' 1 second' ;
> >     ?column?
> >  --------------
> >   00:02:14.987
> >  (1 row)
> 
> Or even easier:
> 
> regression=# select 134987 * interval '1 msec';
>    ?column?   
> --------------
>  00:02:14.987
> (1 row)
> 
> 
>             regards, tom lane
> 

That's the secret, I tried '1 millisecond' . Should have dug further.
--
Adrian Klaver
aklaver@comcast.net

-------------- Original message ----------------------
From: Tom Lane <tgl@sss.pgh.pa.us>
> aklaver@comcast.net (Adrian Klaver) writes:
> > select ('134987'::int/1000.00) * interval ' 1 second' ;
> >     ?column?
> >  --------------
> >   00:02:14.987
> >  (1 row)
> 
> Or even easier:
> 
> regression=# select 134987 * interval '1 msec';
>    ?column?   
> --------------
>  00:02:14.987
> (1 row)
> 
> 
>             regards, tom lane

I just tried this  and got the following which is also what I got when using 'millisecond'

Select 134987 * interval '1 msec';
ERROR:  invalid input syntax for type interval: "1 msec"

SELECT version();                                           version
------------------------------------------------------------------------------------------------PostgreSQL 8.2.3 on
i686-pc-linux-gnu,compiled by GCC gcc (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)
 

--
Adrian Klaver
aklaver@comcast.net

aklaver@comcast.net (Adrian Klaver) writes:
> Select 134987 * interval '1 msec';
> ERROR:  invalid input syntax for type interval: "1 msec"

> SELECT version();
>                                             version
> ------------------------------------------------------------------------------------------------
>  PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)

8.2.3 is your problem --- this is fixed in 8.2.5 and up:
http://archives.postgresql.org/pgsql-committers/2007-05/msg00345.php
        regards, tom lane

-------------- Original message ----------------------
From: Tom Lane <tgl@sss.pgh.pa.us>
> aklaver@comcast.net (Adrian Klaver) writes:
> > Select 134987 * interval '1 msec';
> > ERROR:  invalid input syntax for type interval: "1 msec"
> 
> > SELECT version();
> >                                             version
> > 
> --------------------------------------------------------------------------------
> ----------------
> >  PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 
> (Ubuntu 4.0.3-1ubuntu5)
> 
> 8.2.3 is your problem --- this is fixed in 8.2.5 and up:
> http://archives.postgresql.org/pgsql-committers/2007-05/msg00345.php
> 
>             regards, tom lane

Note to self:
Keep up with bug fixes.

--
Adrian Klaver
aklaver@comcast.net

On Wednesday 26 March 2008 17:14:28 Tom Lane wrote:
> Or even easier:
>
> regression=# select 134987 * interval '1 msec';
>    ?column?  
> --------------
>  00:02:14.987
> (1 row)
>
>
>                         regards, tom lane

Tom and Adrian,

i am trying to incorporate the solution you gave into a function, trying to
save some typing.  Its keeps throwing a syntax error:

edacs=# create or replace function dur_interval_msec(char) returns interval
as 'select ($1 * interval '1 msec');'
language sql
immutable
returns null on null input;
ERROR:  syntax error at or near "1"
LINE 2: as 'select ($1 * interval '1 msec');'                                  ^
obviously it doesn't like the extra single quotes around the 1 msec.  Any
suggestions for a work around?

Shawn

On Wed, Mar 26, 2008 at 5:45 PM, Shawn <postgres@xmtservices.net> wrote:
>  obviously it doesn't like the extra single quotes around the 1 msec.  Any
>  suggestions for a work around?

Use dollar quoting, e.g.:

create or replace function dur_interval_msec(int) returns interval
as
$$
select ($1 * interval '1 msec');
$$
language sql
immutable
returns null on null input;

On Wednesday 26 March 2008 23:12:07 Rodrigo E. De León Plicet wrote:
> Use dollar quoting, e.g.:
>
> create or replace function dur_interval_msec(int) returns interval
> as
> $$
> select ($1 * interval '1 msec');
> $$
> language sql
> immutable
> returns null on null input;

Perfect! Great!  Thank you!

There are so many things about SQL I don't know....

Shawn

Hi Guys,

Just a quick question.  Is there a known problem with dropping function in 
8.2.6?  I can make them but they can't be deleted.

Shawn

On Wednesday 26 March 2008 5:05 pm, Shawn wrote:
> Hi Guys,
>
> Just a quick question.  Is there a known problem with dropping function in
> 8.2.6?  I can make them but they can't be deleted.
>
> Shawn

They will not deleted if something else depends on them i.e. a trigger.
Can you show the error message if any?
-- 
Adrian Klaver
aklaver@comcast.net

On Thursday 27 March 2008 00:13:26 Adrian Klaver wrote:
> On Wednesday 26 March 2008 5:05 pm, Shawn wrote:
> > Hi Guys,
> >
> > Just a quick question.  Is there a known problem with dropping function
> > in 8.2.6?  I can make them but they can't be deleted.
> >
> > Shawn
>
> They will not deleted if something else depends on them i.e. a trigger.
> Can you show the error message if any?
> --
> Adrian Klaver
> aklaver@comcast.net

Thanks Adrian,

Sorry to bother you, I found it.  I had 2 _ characters in the name and I was 
trying to delete with only 1.  Talk about bonehead....

Shawn