pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPart 1 of several - Converting a varchar to an interval
11+ messages / 4 participants
[nested] [flat]
* Part 1 of several - Converting a varchar to an interval
@ 2008-03-26 15:56 Shawn <postgres@xmtservices.net>
0 siblings, 0 replies; 11+ messages in thread
From: Shawn @ 2008-03-26 15:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Part 1 of several - Converting a varchar to an interval
@ 2008-03-26 16:27 Shawn <postgres@xmtservices.net>
0 siblings, 0 replies; 11+ messages in thread
From: Shawn @ 2008-03-26 16:27 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 26 March 2008 16:16:57 you wrote:
> select ('134987'::int/1000.00) * interval ' 1 second' ;
> ?column?
> --------------
> 00:02:14.987
> (1 row)
Thanks Adrian!
Wow!
Shawn
^ permalink raw reply [nested|flat] 11+ messages in thread
* FW: Re: Part 1 of several - Converting a varchar to an interval
@ 2008-03-26 16:32 Adrian Klaver <aklaver@comcast.net>
2008-03-26 17:14 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: aklaver@comcast.net @ 2008-03-26 16:32 UTC (permalink / raw)
To: pgsql-sql
-------------- 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
2008-03-26 16:32 FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
@ 2008-03-26 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-26 22:45 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Shawn <postgres@xmtservices.net>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2008-03-26 17:14 UTC (permalink / raw)
To: aklaver@comcast.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
2008-03-26 16:32 FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
2008-03-26 17:14 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-26 22:45 ` Shawn <postgres@xmtservices.net>
2008-03-26 23:12 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Rodrigo E. De León Plicet <rdeleonp@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Shawn @ 2008-03-26 22:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
2008-03-26 16:32 FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
2008-03-26 17:14 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Tom Lane <tgl@sss.pgh.pa.us>
2008-03-26 22:45 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Shawn <postgres@xmtservices.net>
@ 2008-03-26 23:12 ` Rodrigo E. De León Plicet <rdeleonp@gmail.com>
2008-03-26 23:41 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Shawn <postgres@xmtservices.net>
0 siblings, 1 reply; 11+ messages in thread
From: Rodrigo E. De León Plicet @ 2008-03-26 23:12 UTC (permalink / raw)
To: Shawn <postgres@xmtservices.net>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
2008-03-26 16:32 FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
2008-03-26 17:14 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Tom Lane <tgl@sss.pgh.pa.us>
2008-03-26 22:45 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Shawn <postgres@xmtservices.net>
2008-03-26 23:12 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Rodrigo E. De León Plicet <rdeleonp@gmail.com>
@ 2008-03-26 23:41 ` Shawn <postgres@xmtservices.net>
0 siblings, 0 replies; 11+ messages in thread
From: Shawn @ 2008-03-26 23:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
@ 2008-03-26 17:21 Adrian Klaver <aklaver@comcast.net>
0 siblings, 0 replies; 11+ messages in thread
From: aklaver@comcast.net @ 2008-03-26 17:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
-------------- 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
@ 2008-03-26 17:30 Adrian Klaver <aklaver@comcast.net>
2008-03-26 18:14 ` Re: FW: Re: Part 1 of several - Converting a varchar to an interval Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: aklaver@comcast.net @ 2008-03-26 17:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
-------------- 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
2008-03-26 17:30 Re: FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
@ 2008-03-26 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2008-03-26 18:14 UTC (permalink / raw)
To: aklaver@comcast.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: FW: Re: Part 1 of several - Converting a varchar to an interval
@ 2008-03-26 20:02 Adrian Klaver <aklaver@comcast.net>
0 siblings, 0 replies; 11+ messages in thread
From: aklaver@comcast.net @ 2008-03-26 20:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
-------------- 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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-03-26 23:41 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-26 15:56 Part 1 of several - Converting a varchar to an interval Shawn <postgres@xmtservices.net>
2008-03-26 16:27 Re: Part 1 of several - Converting a varchar to an interval Shawn <postgres@xmtservices.net>
2008-03-26 16:32 FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
2008-03-26 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-26 22:45 ` Shawn <postgres@xmtservices.net>
2008-03-26 23:12 ` Rodrigo E. De León Plicet <rdeleonp@gmail.com>
2008-03-26 23:41 ` Shawn <postgres@xmtservices.net>
2008-03-26 17:21 Re: FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
2008-03-26 17:30 Re: FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
2008-03-26 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-26 20:02 Re: FW: Re: Part 1 of several - Converting a varchar to an interval Adrian Klaver <aklaver@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox