Thread: A few things on intervals

A few things on intervals

From
Alban Hertroys
Date:
I am trying to get infinity in an interval type on PostgreSQL 7.4.7.
I can't seem to get this done, even though there's allegedly an
isfinite(interval) function. For now we use NULL instead, but what would
have worked?

Next to that I noticed:

  select age('infinity'::timestamp);
                        age
-------------------------------------------------
  -292270 years -8 mons -11 days -04:00:54.775807
(1 row)


Another problem we have is that we want to extract the value and the
unit seperately from our intervals, mainly to cast them to something
that can be used in PHP and HTML forms. We don't need to calculate on
them in PHP, but we'd like to split the number and the type into a text
and a drop-down field.
I realize that this isn't possible for intervals like '1 year 3 months',
but we only use relatively simple intervals like '3 months', '6 weeks'
and '100 hours'.

Is there some easy way to query such intervals in a way that can be
reliably split up?

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

Re: A few things on intervals

From
Bruno Wolff III
Date:
On Fri, Apr 28, 2006 at 13:52:09 +0200,
  Alban Hertroys <alban@magproductions.nl> wrote:
> I realize that this isn't possible for intervals like '1 year 3 months',
> but we only use relatively simple intervals like '3 months', '6 weeks'
> and '100 hours'.
>
> Is there some easy way to query such intervals in a way that can be
> reliably split up?

Internally intervals store time in months and seconds (in 7.4.x - 8.1.x also
stores days), so you are not going to be able to do what you want only using
what is stored in an interval. However, you might be able to use that data
combined with your restrictions on allowed values to figure out the desired
type.