pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Howto turn an integer into an interval?
5+ messages / 4 participants
[nested] [flat]
* Howto turn an integer into an interval?
@ 2004-10-05 12:07 Erik Wasser <erik.wasser@iquer.net>
2004-10-05 11:44 ` Re: Howto turn an integer into an interval? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-10-05 12:38 ` Re: Howto turn an integer into an interval? Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-10-06 05:01 ` Re: Howto turn an integer into an interval? sad <sad@bankir.ru>
0 siblings, 3 replies; 5+ messages in thread
From: Erik Wasser @ 2004-10-05 12:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Howto turn an integer into an interval?
2004-10-05 12:07 Howto turn an integer into an interval? Erik Wasser <erik.wasser@iquer.net>
@ 2004-10-05 11:44 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-10-06 08:13 ` Re: Howto turn an integer into an interval? Erik Wasser <erik.wasser@iquer.net>
2 siblings, 1 reply; 5+ messages in thread
From: Achilleus Mantzios @ 2004-10-05 11:44 UTC (permalink / raw)
To: Erik Wasser <erik.wasser@iquer.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Howto turn an integer into an interval?
2004-10-05 12:07 Howto turn an integer into an interval? Erik Wasser <erik.wasser@iquer.net>
2004-10-05 11:44 ` Re: Howto turn an integer into an interval? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-10-06 08:13 ` Erik Wasser <erik.wasser@iquer.net>
0 siblings, 0 replies; 5+ messages in thread
From: Erik Wasser @ 2004-10-06 08:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Howto turn an integer into an interval?
2004-10-05 12:07 Howto turn an integer into an interval? Erik Wasser <erik.wasser@iquer.net>
@ 2004-10-05 12:38 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2 siblings, 0 replies; 5+ messages in thread
From: Pierre-Frédéric Caillaud @ 2004-10-05 12:38 UTC (permalink / raw)
To: Erik Wasser <erik.wasser@iquer.net>; pgsql-sql
try :
resend_interval * '1 seconds'::interval
this will convert your seconds into an interval.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Howto turn an integer into an interval?
2004-10-05 12:07 Howto turn an integer into an interval? Erik Wasser <erik.wasser@iquer.net>
@ 2004-10-06 05:01 ` sad <sad@bankir.ru>
2 siblings, 0 replies; 5+ messages in thread
From: sad @ 2004-10-06 05:01 UTC (permalink / raw)
To: Erik Wasser <erik.wasser@iquer.net>; pgsql-sql
hello
SELECT ('3600'::int::abstime-'epoch'::abstime)::interval;
try to modify this idea to fit your purpose.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-10-06 08:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-05 12:07 Howto turn an integer into an interval? Erik Wasser <erik.wasser@iquer.net>
2004-10-05 11:44 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-10-06 08:13 ` Erik Wasser <erik.wasser@iquer.net>
2004-10-05 12:38 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-10-06 05:01 ` sad <sad@bankir.ru>
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