Postgres Pro
Downloads
Home   >   mailing lists

Thread: absolute value fro timestamps

absolute value fro timestamps

From
"Claudio Lapidus"
Date:
Hello

Evidently is possible to have a negative time interval:

clapidus=> select interval '-1';
 interval
-----------
 -01:00:00

However, there seems to be no provision to get the absolute value in such
case:

clapidus=> select @ interval '-1';
ERROR:  operator does not exist: @ interval
HINT:  No operator matches the given name and argument type(s). You may need
to add explicit typecasts.

clapidus=> select abs(interval '-1');
ERROR:  function abs(interval) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit typecasts.

What did I miss this time?

PGversion is 7.4b2

cl.

Re: absolute value fro timestamps

From
"Claudio Lapidus"
Date:
Bruce Momjian wrote:
> Why would you want an abolute value of a negative interval?

Because I'm trying to match pairs of records that satisfy certain criteria,
one of which is that both records have a timestamp that *may* be slightly
offset between them, so I substract the two and the result must be no
greater than the allowed offset. I don't know which record has the greater
timestamp, so I don't know the sign of the substraction in advance.

>
> This works:
>
> test=> select -(interval '-1');
> ?column?
> ----------
> 01:00:00
> (1 row)
>
> so I suppose you could create a function or CASE statement to get the
> absolute value.
>

In the meantime I implemented it the following way:

\set maxoffset 4
select
   ...
where
    abs(extract(epoch from age(m1.ts, m2.ts))) < :maxoffset
...


Which I think is more compact. Anyway, it would be nice to be able to write
directly

    abs(age(m1.ts, m2.ts))

IMHO.

thanks
cl.

Re: absolute value fro timestamps

From
Bruce Momjian
Date:
Claudio Lapidus wrote:
> Hello
>
> Evidently is possible to have a negative time interval:
>
> clapidus=> select interval '-1';
>  interval
> -----------
>  -01:00:00
>
> However, there seems to be no provision to get the absolute value in such
> case:
>
> clapidus=> select @ interval '-1';
> ERROR:  operator does not exist: @ interval
> HINT:  No operator matches the given name and argument type(s). You may need
> to add explicit typecasts.
>
> clapidus=> select abs(interval '-1');
> ERROR:  function abs(interval) does not exist
> HINT:  No function matches the given name and argument types. You may need
> to add explicit typecasts.
>
> What did I miss this time?

Why would you want an abolute value of a negative interval?

This works:

    test=> select -(interval '-1');
     ?column?
    ----------
     01:00:00
    (1 row)

so I suppose you could create a function or CASE statement to get the
absolute value.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: absolute value fro timestamps

From
Tom Lane
Date:
"Claudio Lapidus" <clapidus@hotmail.com> writes:
> Bruce Momjian wrote:
>> Why would you want an abolute value of a negative interval?

> Because I'm trying to match pairs of records that satisfy certain criteria,

Given that we have a unary-minus operator for intervals, I see no
conceptual objection to having an absolute-value operator (and \do shows
that interval is the only standard datatype that has the former but not
the latter).

However, given that it doesn't seem to be a really widely useful
operator, I think this is the kind of itch that you'll have to scratch
yourself.  Send us a patch and it'll get into the next release ...

            regards, tom lane