Thread: Simple Question

Simple Question

From

Terry Lee Tucker

Date:

12 January 2005, 00:47:36

Hello:

I'm trying to figure out how to convert a floating point value into an 
interval of time. I'm calculating the time required to drive from point A to 
point B. For the sake of this question, we'll just say it is miles/speed. So:

drv_time = 478 / 45.0;

The value of this is: 10.6222222222222222

Is there a way of converting this value to an interval. It seems that INTERVAL 
only works with a quoted literal value.

If I type:
rnd=# select interval '10.8444444444444444 hours';         interval
----------------------------@ 10 hours 50 mins 40 secs
(1 row)

Anybody have an pointers?

Thanks...

Work: 1-336-372-6812Cell: 1-336-363-4719
email: terry@esc1.com

Re: Simple Question

From

Guy Fraser

Date:

12 January 2005, 01:07:25

Convert to seconds first (3600 sec/hr) :

select ('3600'::int4 * '478'::int4 / '45.0'::float8
)::int4::reltime::interval ;interval
----------10:37:20
(1 row)

I don't know if "::int4::reltime::interval" is the best 
way to end up with an interval, but its the only way I 
could figure out how to do it off the top of my head.

On Tue, 2005-11-01 at 16:42 -0500, Terry Lee Tucker wrote:
> Hello:
> 
> I'm trying to figure out how to convert a floating point value into an 
> interval of time. I'm calculating the time required to drive from point A to 
> point B. For the sake of this question, we'll just say it is miles/speed. So:
> 
> drv_time = 478 / 45.0;
> 
> The value of this is: 10.6222222222222222
> 
> Is there a way of converting this value to an interval. It seems that INTERVAL 
> only works with a quoted literal value.
> 
> If I type:
> rnd=# select interval '10.8444444444444444 hours';
>           interval
> ----------------------------
>  @ 10 hours 50 mins 40 secs
> (1 row)
> 
> Anybody have an pointers?
> 
> Thanks...
> 
> 
>  Work: 1-336-372-6812
>  Cell: 1-336-363-4719
> email: terry@esc1.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
-- 
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787

Re: Simple Question

From

Terry Lee Tucker

Date:

12 January 2005, 01:12:31

I figured it out. This works:

travel_window INTERVAL;
drv_time FLOAT;

drv_time = miles / 45.0;                -- drive time
travel_window = quote_literal(drv_time || '' hours'');

The variable, travel_window becomes:  @ 10 hours 50 mins 40 secs, which is 
what I wanted.

If anybody knows any other ways, I'd be interested in see that too.

On Tuesday 11 January 2005 04:42 pm, Terry Lee Tucker saith:
> Hello:
>
> I'm trying to figure out how to convert a floating point value into an
> interval of time. I'm calculating the time required to drive from point A
> to point B. For the sake of this question, we'll just say it is
> miles/speed. So:
>
> drv_time = 478 / 45.0;
>
> The value of this is: 10.6222222222222222
>
> Is there a way of converting this value to an interval. It seems that
> INTERVAL only works with a quoted literal value.
>
> If I type:
> rnd=# select interval '10.8444444444444444 hours';
>           interval
> ----------------------------
>  @ 10 hours 50 mins 40 secs
> (1 row)
>
> Anybody have an pointers?
>
> Thanks...
>
>
>  Work: 1-336-372-6812
>  Cell: 1-336-363-4719
> email: terry@esc1.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Work: 1-336-372-6812Cell: 1-336-363-4719
email: terry@esc1.com

Re: Simple Question

From

Michael Fuhr

Date:

12 January 2005, 01:16:26

On Tue, Jan 11, 2005 at 04:42:21PM -0500, Terry Lee Tucker wrote:

> Subject: [SQL] Simple Question

Please use a more descriptive subject -- think about how somebody
looking at a list of 200 messages, all with subjects like "Simple
Question" or "PostgreSQL Question," would decide to look at yours.

> drv_time = 478 / 45.0;
> 
> The value of this is: 10.6222222222222222
> 
> Is there a way of converting this value to an interval. It seems that INTERVAL 
> only works with a quoted literal value.

You can do arithmetic on intervals:

SELECT 478 / 45.0 * interval'1 hour';

For more information, see "Date/Time Functions and Operators" in
the documentation.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Simple Question

From

Stephan Szabo

Date:

12 January 2005, 01:21:27

On Tue, 11 Jan 2005, Terry Lee Tucker wrote:

> Hello:
>
> I'm trying to figure out how to convert a floating point value into an
> interval of time. I'm calculating the time required to drive from point A to
> point B. For the sake of this question, we'll just say it is miles/speed. So:
>
> drv_time = 478 / 45.0;
>
> The value of this is: 10.6222222222222222

I think something like478/45.0 * interval '1 hour'
may do what you want.

Re: Simple Question

From

Terry Lee Tucker

Date:

12 January 2005, 01:23:43

Thank you for the reply in spite of the subject.

On Tuesday 11 January 2005 05:15 pm, Michael Fuhr saith:
> On Tue, Jan 11, 2005 at 04:42:21PM -0500, Terry Lee Tucker wrote:
> > Subject: [SQL] Simple Question
>
> Please use a more descriptive subject -- think about how somebody
> looking at a list of 200 messages, all with subjects like "Simple
> Question" or "PostgreSQL Question," would decide to look at yours.

I will do this in the future.

>
> > drv_time = 478 / 45.0;
> >
> > The value of this is: 10.6222222222222222
> >
> > Is there a way of converting this value to an interval. It seems that
> > INTERVAL only works with a quoted literal value.
>
> You can do arithmetic on intervals:
>
> SELECT 478 / 45.0 * interval'1 hour';

I like your soultion better than mine. Thanks for the answer.

>
> For more information, see "Date/Time Functions and Operators" in
> the documentation.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
Work: 1-336-372-6812Cell: 1-336-363-4719
email: terry@esc1.com

Re: Simple Question

From

Terry Lee Tucker

Date:

12 January 2005, 01:26:15

Thanks for the reply. My answer was a little different than yours because I 
used 488 instead of 478. Well, that three ways so far ;o)

On Tuesday 11 January 2005 05:06 pm, Guy Fraser saith:
> Convert to seconds first (3600 sec/hr) :
>
> select (
>  '3600'::int4
>  * '478'::int4
>  / '45.0'::float8
> )::int4::reltime::interval ;
>  interval
> ----------
>  10:37:20
> (1 row)
>
> I don't know if "::int4::reltime::interval" is the best
> way to end up with an interval, but its the only way I
> could figure out how to do it off the top of my head.
>
> On Tue, 2005-11-01 at 16:42 -0500, Terry Lee Tucker wrote:
> > Hello:
> >
> > I'm trying to figure out how to convert a floating point value into an
> > interval of time. I'm calculating the time required to drive from point A
> > to point B. For the sake of this question, we'll just say it is
> > miles/speed. So:
> >
> > drv_time = 478 / 45.0;
> >
> > The value of this is: 10.6222222222222222
> >
> > Is there a way of converting this value to an interval. It seems that
> > INTERVAL only works with a quoted literal value.
> >
> > If I type:
> > rnd=# select interval '10.8444444444444444 hours';
> >           interval
> > ----------------------------
> >  @ 10 hours 50 mins 40 secs
> > (1 row)
> >
> > Anybody have an pointers?
> >
> > Thanks...
> >
> >
> >  Work: 1-336-372-6812
> >  Cell: 1-336-363-4719
> > email: terry@esc1.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> --
> Guy Fraser
> Network Administrator
> The Internet Centre
> 1-888-450-6787
> (780)450-6787
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
Work: 1-336-372-6812Cell: 1-336-363-4719
email: terry@esc1.com

Re: Simple Question

From

Tom Lane

Date:

12 January 2005, 01:43:50

Terry Lee Tucker <terry@esc1.com> writes:
> I'm trying to figure out how to convert a floating point value into an 
> interval of time.

Use something like

regression=# select  (478 / 45.0) * '1 hour'::interval;?column?
----------10:37:20
(1 row)

... or whatever other scale factor you have in mind.
        regards, tom lane