Обсуждение: infinity interval

Hello,

how can I store an infinity value into an interval? I want to store
offsets to a timestamp, and for some cases i need +infinity and
-infinity as result.

I want to make something like this:
SELECT now() + 'infinity'::interval;

This should return 'infinity'::timestamp.

Is it possible?

Thanks,
  Gerhard

On Sat, Dec 20, 2008 at 3:52 PM, Gerhard Heift
<ml-postgresql-20081012-3518@gheift.de> wrote:

> how can I store an infinity value into an interval?

I don't think you can:

postgres=# select INTERVAL '1 week' + 'infinity'::timestamp;
 ?column?
----------
 infinity
(1 row)

postgres=# select INTERVAL 'infinity' + now();
ERROR:  invalid input syntax for type interval: "infinity"

Also notice the limits that the Interval datatype supports.
http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html

> I want to store
> offsets to a timestamp, and for some cases i need +infinity and
> -infinity as result.

You might need to rethink your design to two timestamps, or use a null
represent to concept of infinite duration since postgresql doesn't
currently support this.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Gerhard-
did you try to use a PL/SQL Block to initialize the necessary integer values in your declare block e.g.

DECLARE
SET @INT_MIN = INTEGER ( min ); --Min Value
SET @INT_MAX = INTEGER ( max ); --Max value
BEGIN
....
END;

http://pgscript.projects.postgresql.org/SCRIPT.html

HTH
Martin

______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.




> Date: Sun, 21 Dec 2008 00:52:04 +0100
> From: ml-postgresql-20081012-3518@gheift.de
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] infinity interval
>
> Hello,
>
> how can I store an infinity value into an interval? I want to store
> offsets to a timestamp, and for some cases i need +infinity and
> -infinity as result.
>
> I want to make something like this:
> SELECT now() + 'infinity'::interval;
>
> This should return 'infinity'::timestamp.
>
> Is it possible?
>
> Thanks,
> Gerhard

---

Send e-mail faster without improving your typing skills. Get your Hotmail® account.

Gerhard Heift wrote:
> how can I store an infinity value into an interval? I want to store
> offsets to a timestamp, and for some cases i need +infinity and
> -infinity as result.
>
> I want to make something like this:
> SELECT now() + 'infinity'::interval;
>
> This should return 'infinity'::timestamp.
>
> Is it possible?

TODO has:

    o Allow infinite intervals just like infinite timestamps

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +