Lists: | pgsql-general |
---|
From: | "Claudio Lapidus" <clapidus(at)hotmail(dot)com> |
---|---|
To: | "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: absolute value fro timestamps |
Date: | 2003-08-29 21:44:07 |
Message-ID: | BAY7-DAV34vJj3LsGlo000062c7@hotmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | "Claudio Lapidus" <clapidus(at)hotmail(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | absolute value fro timestamps |
Date: | 2003-09-02 16:19:53 |
Message-ID: | BAY7-DAV3760mJWEsBk00004fce@hotmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Claudio Lapidus <clapidus(at)hotmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: absolute value fro timestamps |
Date: | 2003-09-03 21:13:45 |
Message-ID: | 200309032113.h83LDjc28519@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Claudio Lapidus" <clapidus(at)hotmail(dot)com> |
Cc: | "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: absolute value fro timestamps |
Date: | 2003-09-04 03:03:13 |
Message-ID: | 5600.1062644593@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Claudio Lapidus" <clapidus(at)hotmail(dot)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