Thread: Howto turn an integer into an interval?

Hi List,

I got a table named foobar with two fields

last_sms         is a 'timestamp without timezone'
resend_interval is a 'integer'

I choose 'integer' for resend_interval rather than a 'interval' because 
the frontend can easier handle the number of seconds. But now I'm stuck 
with the query to get all rows that we're older than 'resend_interval' 
in seconds.

My first try was:

SELECT *  FROM sms_groups  WHERE    (current_timestamp - last_sms) > '3600 seconds'::interval

This is working great and returns all rows that are older than a hour. 
But how I do this with the colum 'resend_interval' in the query?

SELECT *  FROM sms_groups  WHERE    (current_timestamp - last_sms) > resend_interval::interval

gives me only a "cannot cast type integer to interval". So how can I 
turn an integer to an interval? Or is there an other solution? 
Suggestions?

P.S: I'm using psql '7.4.5' of gentoo.

-- 
So long... Fuzz

O Erik Wasser έγραψε στις Oct 5, 2004 :

> Hi List,
> 
> I got a table named foobar with two fields
> 
> last_sms         is a 'timestamp without timezone'
> resend_interval is a 'integer'
> 
> I choose 'integer' for resend_interval rather than a 'interval' because 
> the frontend can easier handle the number of seconds. But now I'm stuck 
> with the query to get all rows that we're older than 'resend_interval' 
> in seconds.
> 
> My first try was:
> 
> SELECT *
>    FROM sms_groups
>    WHERE
>      (current_timestamp - last_sms) > '3600 seconds'::interval
> 
> This is working great and returns all rows that are older than a hour. 
> But how I do this with the colum 'resend_interval' in the query?
> 
> SELECT *
>    FROM sms_groups
>    WHERE
>      (current_timestamp - last_sms) > resend_interval::interval
> 
> gives me only a "cannot cast type integer to interval". So how can I 
> turn an integer to an interval? Or is there an other solution? 
> Suggestions?

If you know for sure that you are keeping resend_interval in seconds,
then try as follows:

foodb=# SELECT (59::text||' secs')::interval;interval
----------00:00:59
(1 row)

foodb=# SELECT (120::text||' secs')::interval;interval
----------00:02:00
(1 row)

foodb=# SELECT ((3600*25)::text||' secs')::interval;   interval
----------------1 day 01:00:00
(1 row)

foodb=#


> 
> P.S: I'm using psql '7.4.5' of gentoo.
> 
> 

-- 
-Achilleus

try :
resend_interval * '1 seconds'::interval
this will convert your seconds into an interval.

hello

SELECT ('3600'::int::abstime-'epoch'::abstime)::interval;

try to modify this idea to fit your purpose.

On Tuesday 05 October 2004 13:44, you wrote:

> If you know for sure that you are keeping resend_interval in seconds,
> then try as follows:
>
> foodb=# SELECT (59::text||' secs')::interval;
>  interval
> ----------
>  00:00:59
> (1 row)


Thanks for this solution and the others. It's now working very fine. B-)

-- 
So long... Fuzz