Re: Howto turn an integer into an interval?

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Howto turn an integer into an interval?

try :

resend_interval * '1 seconds'::interval

this will convert your seconds into an interval.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Howto turn an integer into an interval?

hello

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

try to modify this idea to fit your purpose.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Howto turn an integer into an interval?

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)