Absolute value of intervals

[Joshua Berry]

I couldn't find the operator '@' for intervals and found this thread
from over six years ago:
http://archives.postgresql.org/pgsql-general/2003-09/msg00292.php

| "Claudio Lapidus" <***@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

Is this is the case now? I have some data that is related but requires
fuzzy joining on timestamps within a time interval.

I'd like to be able to do this:
select * from enviados e, recibidos r where @ (e.fecha - r.fecha) <
interval '1 second'

rather than this:
select * from enviados e, recibidos r where (e.fecha - r.fecha) <
interval '1 second' AND (r.fecha - e.fecha) < interval '1 second'

or this:
select * from enviados e, recibidos r where (r.fecha + interval '1
seconds', r.fecha - interval '1 seconds') OVERLAPS (e.fecha, e.fecha);

If such an operator doesn't exist yet, I'm keen to try to generate a
patch and tests; but I could use some pointers as to which project
files that should be related to such a change.

Regards,
-Joshua Berry

[Sam Mason]

A simple SQL implementation would look like:

CREATE FUNCTION absinterval(interval) RETURNS interval
IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';
CREATE OPERATOR @ ( PROCEDURE = absinterval, RIGHTARG = interval );

or is a C version really needed?

[Tom Lane]

Post by Sam Mason
CREATE FUNCTION absinterval(interval) RETURNS interval
IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';
or is a C version really needed?

I think this came up again recently and somebody pointed out that the
correct definition isn't as obvious as all that. The components of
an interval can have different signs, so should abs('-1 day 1 hour') be
'1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
'1 day -25 hours'?

regards, tom lane

[Sam Mason]

Post by Tom Lane

Post by Sam Mason
CREATE FUNCTION absinterval(interval) RETURNS interval
IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';

I think this came up again recently and somebody pointed out that the
correct definition isn't as obvious as all that.

Hum, I think it is! :)

Post by Tom Lane
The components of
an interval can have different signs, so should abs('-1 day 1 hour') be
'1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
'1 day -25 hours'?

Funny, I used exactly that example when playing---although I spelled it
'-1 day 25:00:00'!

It all comes down to how you define things. I'd say my quick hack does
the "right" thing, but yes I should have pointed out that the interval
type has subs-structure that makes it's behavior non-obvious. My
intuition as to why it's correct worked along these lines:

1) '10' can be defined as '1 hundred -90 units'.
2.1) negating '10' gives '-10'.
2.2) negating the other gives '-1 hundred 90 units'.
3) give 'hundred' the value of '100' and 'units' the value '1' and
check if things sum up.

If the absolute value of an interval was defined to strip out all the
negation signs you'd get the "wrong" answers out. The awkward thing
with intervals is the the components are not all of the same units, but
I think the argument stands.

[Sam Mason]

Post by Sam Mason
If the absolute value of an interval was defined to strip out all the
negation signs you'd get the "wrong" answers out.

Oops, forgot another reason! For maths to work (n) and (-(-n)) should
evaluate to the same value. Inverting all the signs, as negation does,
will ensure that these semantics remain.

[Jasen Betts]

Post by Sam Mason

Post by Sam Mason
If the absolute value of an interval was defined to strip out all the
negation signs you'd get the "wrong" answers out.

Oops, forgot another reason! For maths to work (n) and (-(-n)) should
evaluate to the same value. Inverting all the signs, as negation does,
will ensure that these semantics remain.

There not requrement in mathematics that

z be a member of the set { abs(z) , -abs(z) }

consider the case of z=sqrt(-1)

[Sam Mason]

Post by Jasen Betts

Post by Sam Mason

Post by Sam Mason
If the absolute value of an interval was defined to strip out all the
negation signs you'd get the "wrong" answers out.

Oops, forgot another reason! For maths to work (n) and (-(-n)) should
evaluate to the same value. Inverting all the signs, as negation does,
will ensure that these semantics remain.

Hum, I'm not entirely sure what I was thinking when I wrote the above.
it's got nothing to do with taking absolute values!

Post by Jasen Betts
There not requrement in mathematics that
z be a member of the set { abs(z) , -abs(z) }
consider the case of z=sqrt(-1)

That said, I don't follow your point at all.

[Jasen Betts]

Post by Sam Mason

Post by Tom Lane

Post by Sam Mason
CREATE FUNCTION absinterval(interval) RETURNS interval
IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';

I think this came up again recently and somebody pointed out that the
correct definition isn't as obvious as all that.

Hum, I think it is! :)

Post by Tom Lane
The components of
an interval can have different signs, so should abs('-1 day 1 hour') be
'1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
'1 day -25 hours'?

Funny, I used exactly that example when playing---although I spelled it
'-1 day 25:00:00'!
It all comes down to how you define things. I'd say my quick hack does
the "right" thing, but yes I should have pointed out that the interval
type has subs-structure that makes it's behavior non-obvious. My
1) '10' can be defined as '1 hundred -90 units'.

not all days are 24 hours long,
some differ by one second or one hour from that.
months are even worse.

Post by Sam Mason
If the absolute value of an interval was defined to strip out all the
negation signs you'd get the "wrong" answers out.

I think a definition that defines abs(interval)

such that

extract( date-part from abs(interval)) = abs(extract( date-part from interval))

is not totally without merit.

Post by Sam Mason
The awkward thing
with intervals is the the components are not all of the same units, but
I think the argument stands.

the awkward thing is that the units are not all related by fixed ratios.

60 minutes per hour
7 days per week
12 months per year

thses are not:

days per month, (gregorian calendar)
days per year, (leap year)
hours per day, (daylight saving)
and seconds per minute (leap second)

[Scott Bailey]

Post by Tom Lane
I think this came up again recently and somebody pointed out that the
correct definition isn't as obvious as all that. The components of
an interval can have different signs, so should abs('-1 day 1 hour') be
'1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
'1 day -25 hours'?

I agree with Sam. The absolute value of a negative interval should be
equidistant from zero, not the removal of negative signs. So abs('-1 day
1 hour') should be ('1 day -1 hour'). I don't think your corner case is
any different. So his function and operator should be perfectly valid.

But there is some ambiguity around the length of a month. So INTERVAL '1
month - 30 days' = INTERVAL '0 days' = INTERVAL '-1 month +30 days'.
But when added to a date, it makes no change for months with 30 days,
adds 1 day for months with 31 days and subtracts 2 days for February.

Scott Bailey

[Jeff Davis]

Post by Scott Bailey
But there is some ambiguity around the length of a month. So INTERVAL '1
month - 30 days' = INTERVAL '0 days' = INTERVAL '-1 month +30 days'.
But when added to a date, it makes no change for months with 30 days,
adds 1 day for months with 31 days and subtracts 2 days for February.

Yes, that is a strange case. When you can't tell if an interval is
positive or negative, how do you define the absolute value?

I think that's a strong argument not to provide an absolute value
function for INTERVALs.

Regards,
Jeff Davis

[Tom Lane]

Post by Jeff Davis
Yes, that is a strange case. When you can't tell if an interval is
positive or negative, how do you define the absolute value?

That was the point of my '1 day -25 hours' example. Whether you
consider that positive or negative seems mighty arbitrary.

regards, tom lane

[Sam Mason]

Post by Tom Lane

Post by Jeff Davis
Yes, that is a strange case. When you can't tell if an interval is
positive or negative, how do you define the absolute value?

That was the point of my '1 day -25 hours' example. Whether you
consider that positive or negative seems mighty arbitrary.

My personal feeling is that when you provide any ordering operator and
negation you can easily provide an absolute value operator. We've
already (somewhat arbitrarily) decided that one of '1month -30days' and
'-1month 30days) is "greater" than the other, so why not provide an
operator that returns the "greater" of an interval value and its own
negation?

[Scott Bailey]

Post by Sam Mason
My personal feeling is that when you provide any ordering operator and
negation you can easily provide an absolute value operator. We've
already (somewhat arbitrarily) decided that one of '1month -30days' and
'-1month 30days) is "greater" than the other, so why not provide an
operator that returns the "greater" of an interval value and its own
negation?

Technically, greater doesn't arbitrarily decide one is greater than the
other. It determines the two are equivalent and (correctly) chooses the
leftmost one.

I think it is important to separate the concept of an interval with
addition of an interval with a timestamp. By (the interval type's)
definition a day is 24 hours, a month is 30 days, a year is 365.25 days.
And the user needs to understand that abs and extract epoch do their
calculations based on those definitions rather than what would happen
when applied to an arbitrary timestamp.

To say that extract epoch can determine the number of seconds in an
interval, while saying that you can not determine the absolute value of
an interval is not logical. Either you can do both or you can do neither.

Postgres intervals internally have an 8 byte microsecond part, a 4 byte
day part and a 4 byte month part. I would argue that there is no
ambiguity with the second (technically microsecond), and day parts of
intervals and that ambiguity is introduced with the month part. A day is
always 24 hours UTC. (However some times our timezones change.) And we
ignore leap seconds. All intervals that result timestamp subtraction
ONLY use the microsecond and day pieces in the resulting interval. This
is probably why most other databases have two interval types. One for
storing precise intervals (DAY TO SECOND) and one for fuzzy intervals
(YEAR TO MONTH).

Now I think that Postgres' interval implementation is much nicer to work
with than the others. But perhaps things like extract epoch and abs
should exhibit different behaviors when the month part is used.

Consider the following:
SELECT mos,
EXTRACT(EPOCH FROM INTERVAL '1 month' * mos) / 86400 AS days
FROM generate_series(9, 26) mos;

mos | days
-----+--------
9 | 270
10 | 300
11 | 330
12 | 365.25
13 | 395.25
14 | 425.25
15 | 455.25
16 | 485.25
17 | 515.25
18 | 545.25
19 | 575.25
20 | 605.25
21 | 635.25
22 | 665.25
23 | 695.25
24 | 730.5
25 | 760.5
26 | 790.5

[Alban Hertroys]

Post by Scott Bailey

Post by Sam Mason
My personal feeling is that when you provide any ordering operator and
negation you can easily provide an absolute value operator. We've
already (somewhat arbitrarily) decided that one of '1month -30days' and
'-1month 30days) is "greater" than the other, so why not provide an
operator that returns the "greater" of an interval value and its own
negation?

Technically, greater doesn't arbitrarily decide one is greater than
the other. It determines the two are equivalent and (correctly)
chooses the leftmost one.
I think it is important to separate the concept of an interval with
addition of an interval with a timestamp. By (the interval type's)
definition a day is 24 hours, a month is 30 days, a year is 365.25
days. And the user needs to understand that abs and extract epoch do
their calculations based on those definitions rather than what would
happen when applied to an arbitrary timestamp.

There's a slight complication to this approach; what happens if you
ask for <timestamp> + abs(<interval>)?

You don't want to calculate the result of abs() based on a 24h day, a
30d month and a 365.25d year as there is a timestamp to base your
calculations on, but AFAIK you can't see that from within the abs()
function implementation. Unless you store that information in the
context somehow.


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4aec24e711071499813979!

[Sam Mason]

Post by Scott Bailey

Post by Sam Mason
My personal feeling is that when you provide any ordering operator and
negation you can easily provide an absolute value operator. We've
already (somewhat arbitrarily) decided that one of '1month -30days' and
'-1month 30days) is "greater" than the other, so why not provide an
operator that returns the "greater" of an interval value and its own
negation?

Technically, greater doesn't arbitrarily decide one is greater than the
other. It determines the two are equivalent and (correctly) chooses the
leftmost one.

where "correctly" has various provisos attached.

Post by Scott Bailey
I think it is important to separate the concept of an interval with
addition of an interval with a timestamp. By (the interval type's)
definition a day is 24 hours, a month is 30 days, a year is 365.25 days.

When I was saying "arbitrary" above; it was in choosing these numbers.
They're reasonable defaults that do the right thing most of the time,
but it's possible to have other values that would give better results in
certain (rare) situations. I don't think we want to go changing things
though, the current values are what most people expect.

Post by Scott Bailey
To say that extract epoch can determine the number of seconds in an
interval, while saying that you can not determine the absolute value of
an interval is not logical. Either you can do both or you can do neither.

Yes, I agree.

Post by Scott Bailey
perhaps things like extract epoch and abs
should exhibit different behaviors when the month part is used.
mos | days
11 | 330
12 | 365.25

You mean that it should trunc() the result of the months part to
complete days? Instead of doing:

result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);

it should be doing:

result += trunc((interval->month / MONTHS_PER_YEAR) * DAYS_PER_YEAR) * SECS_PER_DAY;

? Not sure if a change such as this could be made though.

[Marko Kreen]

Post by Tom Lane

Post by Jeff Davis
Yes, that is a strange case. When you can't tell if an interval is
positive or negative, how do you define the absolute value?

That was the point of my '1 day -25 hours' example. Whether you
consider that positive or negative seems mighty arbitrary.

If I can add it to a timestamp and get a deterministic result,
then we already have decided how to interpret the arbitrariness.

Might as well be consistent then.

[Sam Mason]

Post by Marko Kreen

Post by Tom Lane
That was the point of my '1 day -25 hours' example. Whether you
consider that positive or negative seems mighty arbitrary.

If I can add it to a timestamp and get a deterministic result,
then we already have decided how to interpret the arbitrariness.

The point is that it's only in relation to a specific timestamp do the
components of an interval actually receive comparable values. For
example, a day can be a varying number of hours, a month a varying
number of days and a year can also vary in its number of days. Once
you add this to a date then these components get fixed, but without any
specific timestamp to work with these components remain undefined.

I'd argue that it doesn't help to know that we can add it to a timestamp
and get out a deterministic result. It's the fact that we have a
deterministic comparison operator that helps us.

[Marko Kreen]

Post by Sam Mason

Post by Marko Kreen

Post by Tom Lane
That was the point of my '1 day -25 hours' example. Whether you
consider that positive or negative seems mighty arbitrary.

If I can add it to a timestamp and get a deterministic result,
then we already have decided how to interpret the arbitrariness.

The point is that it's only in relation to a specific timestamp do the
components of an interval actually receive comparable values. For
example, a day can be a varying number of hours, a month a varying
number of days and a year can also vary in its number of days. Once
you add this to a date then these components get fixed, but without any
specific timestamp to work with these components remain undefined.
I'd argue that it doesn't help to know that we can add it to a timestamp
and get out a deterministic result. It's the fact that we have a
deterministic comparison operator that helps us.

Slightly makes sense, but only slightly. We deterministically know,
that we dont have certain timestamp, thus we need to use some default
values. We already have situation that does that:

extract(epoch from interval)

Yes, some cases the value returned is not the same value that would
be added to a specific timestamp, but so what? How is current situation
better that we force users to manually create potentially buggy
equivalent functionality?

[Sam Mason]

Post by Marko Kreen
Slightly makes sense, but only slightly. We deterministically know,
that we dont have certain timestamp, thus we need to use some default
extract(epoch from interval)

You're arguing the same point as me. Your extract code and my
comparison operator use exactly the same values as defaults when
normalizing their respective intervals. Neither of them have anything
to do with timestamps.

Post by Marko Kreen
Yes, some cases the value returned is not the same value that would
be added to a specific timestamp, but so what? How is current situation
better that we force users to manually create potentially buggy
equivalent functionality?

Tom was arguing that it's fundamentally inappropriate to ask for the
absolute value of an interval. I was saying that we've already chosen
arbitrary values for the components of an interval for comparison and
you've just pointed out that we use the same values elsewhere. Once
we've chosen them I don't see why we shouldn't extend them to all the
places that they seem to fit, such as this absolute value operator.

I think the attached trivial bit code should do the right thing, however
I don't know what else is needed to hook everything up.

[Alvaro Herrera]

+ Datum
+ interval_abs(PG_FUNCTION_ARGS)
+ {
+ Interval *interval1 = PG_GETARG_INTERVAL_P(0);
+ Interval *interval2 = PG_GETARG_INTERVAL_P(1);

Surely it must receive a single argument?

[Sam Mason]

Post by Alvaro Herrera

+ Datum
+ interval_abs(PG_FUNCTION_ARGS)
+ {
+ Interval *interval1 = PG_GETARG_INTERVAL_P(0);
+ Interval *interval2 = PG_GETARG_INTERVAL_P(1);

Surely it must receive a single argument?

Indeed it must, trying to write other code at the same time is a good
recipe for getting myself in a mess!

[Lew]

Post by Scott Bailey
But there is some ambiguity around the length of a month. So INTERVAL '1
month - 30 days' = INTERVAL '0 days' = INTERVAL '-1 month +30 days'.
But when added to a date, it makes no change for months with 30 days,
adds 1 day for months with 31 days and subtracts 2 days for February.

or 1 day for February, 2012.

[Lew]

Post by Tom Lane

Post by Sam Mason
CREATE FUNCTION absinterval(interval) RETURNS interval
IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';
or is a C version really needed?

I think this came up again recently and somebody pointed out that the
correct definition isn't as obvious as all that. The components of
an interval can have different signs, so should abs('-1 day 1 hour') be
'1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
'1 day -25 hours'?

I'm writing this at about 8:35 p.m. New York time on October 31, 2009. From
now, adding interval '1 day -25 hours' yields right now, New York time.

[Jasen Betts]

Post by Lew

Post by Tom Lane

Post by Sam Mason
CREATE FUNCTION absinterval(interval) RETURNS interval
IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';
or is a C version really needed?

I think this came up again recently and somebody pointed out that the
correct definition isn't as obvious as all that. The components of
an interval can have different signs, so should abs('-1 day 1 hour') be
'1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
'1 day -25 hours'?

I'm writing this at about 8:35 p.m. New York time on October 31, 2009. From
now, adding interval '1 day -25 hours' yields right now, New York time.

This year halloween is the longest night. Interesting.

[Alvaro Herrera]

Post by Jasen Betts

Post by Lew
I'm writing this at about 8:35 p.m. New York time on October 31, 2009. From
now, adding interval '1 day -25 hours' yields right now, New York time.

This year halloween is the longest night. Interesting.

Not the first time this happens, see
http://www.amazon.com/Batman-Long-Halloween-Jeph-Loeb/dp/1563894696

[Scott Bailey]

Post by Joshua Berry
http://archives.postgresql.org/pgsql-general/2003-09/msg00292.php
| >> 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
Is this is the case now? I have some data that is related but requires
fuzzy joining on timestamps within a time interval.
interval '1 second'
select * from enviados e, recibidos r where (e.fecha - r.fecha) <
interval '1 second' AND (r.fecha - e.fecha) < interval '1 second'
select * from enviados e, recibidos r where (r.fecha + interval '1
seconds', r.fecha - interval '1 seconds') OVERLAPS (e.fecha, e.fecha);
If such an operator doesn't exist yet, I'm keen to try to generate a
patch and tests; but I could use some pointers as to which project
files that should be related to such a change.
Regards,
-Joshua Berry

You should test for a positive or negative interval against INTERVAL '0
seconds' because you can have a positive interval that is a fraction of
a second.

But we've got two projects that implement a period data type, pgTemporal
and Chronos.
http://pgfoundry.org/projects/temporal/
http://pgfoundry.org/projects/timespan/

Scott Bailey

[Jasen Betts]

Post by Joshua Berry
http://archives.postgresql.org/pgsql-general/2003-09/msg00292.php
| >> 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
Is this is the case now? I have some data that is related but requires
fuzzy joining on timestamps within a time interval.

what's the absolute value of '1month -30 days'::interval

if I add it to the first of march it goes forwards if I add it to the
first of february if goes backwards. if I add it to the first of april
it goes nowhere.

select '2008-02-01'::date + '1month -30days'::interval;
select '2008-03-01'::date + '1month -30days'::interval;
select '2008-04-01'::date + '1month -30days'::interval;

I'm guessing that '1 month 30 days' is the only mathematically
consistent answer, not that it's likely to be useful.




"People don't understand time, it's not what you think it is"
...
"It's more like a big ball of wibbly wobbly timey wimely stuff" - Dr Who.
("Blink")

[Sam Mason]

Post by Jasen Betts
what's the absolute value of '1month -30 days'::interval
if I add it to the first of march it goes forwards if I add it to the
first of february if goes backwards. if I add it to the first of april
it goes nowhere.
select '2008-02-01'::date + '1month -30days'::interval;
select '2008-03-01'::date + '1month -30days'::interval;
select '2008-04-01'::date + '1month -30days'::interval;
I'm guessing that '1 month 30 days' is the only mathematically
consistent answer, not that it's likely to be useful.

Except that this is not consistent with itself. Most definitions have
the absolute value of a number being the same as either the number
itself or its negation.

[Ron Mayer]

Post by Jasen Betts
what's the absolute value of '1month -30 days'::interval

Curious what a use case for taking the absolute value
of such mixed intervals might be.

I could imagine such intervals being used for stuff like
"XXX is due in Y months but needs to be submitted Z days earlier".

But I can't really imagine worrying about if it's positive
or negative until it's evaluated at a particular date (i.e.
reporting "XXX has to be submitted in N days" or
"XXX was supposed to be submitted N days ago").

[CaT]

Post by Jasen Betts
what's the absolute value of '1month -30 days'::interval
if I add it to the first of march it goes forwards if I add it to the

In march a month has 31 days.

Post by Jasen Betts
first of february if goes backwards. if I add it to the first of april

29 or 28 days.

Post by Jasen Betts
it goes nowhere.

30 days so the math below is 0.

Post by Jasen Betts
select '2008-02-01'::date + '1month -30days'::interval;
select '2008-03-01'::date + '1month -30days'::interval;
select '2008-04-01'::date + '1month -30days'::interval;
I'm guessing that '1 month 30 days' is the only mathematically
consistent answer, not that it's likely to be useful.

A month is not fixed width. It depends on which month you're talking
about.

And then there is http://en.wikipedia.org/wiki/Month. Ow.

Having given it some more 1am thinking the above probably works out to:

select '2008-02-01'::date + '1month -30days'::interval;

2008-02-01 + 1 month = 2008-03-01
2008-03-01 - 30 days = 2008-01-31 (feb has 29 days)

select '2008-03-01'::date + '1month -30days'::interval;

2008-03-01 + 1 month = 2008-04-01
2008-04-01 - 30 days = 2008-03-02 (march has 31 days)

select '2008-04-01'::date + '1month -30days'::interval;

2008-04-01 + 1 month = 2008-05-01
2008-05-01 - 30 days = 2008-04-01 (april has 30 days)

I'm guessing (no postgres avail to me atm) and half asleep so my math
my be boogered.

In the end, though, it all makes sense. :)

[Jasen Betts]

Post by Joshua Berry
http://archives.postgresql.org/pgsql-general/2003-09/msg00292.php
| >> 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
Is this is the case now? I have some data that is related but requires
fuzzy joining on timestamps within a time interval.
interval '1 second'

for this case: convert to seconds and then do abs.

select * from enviados e, recibidos r where abs(extract ( epoch from
(e.fecha - r.fecha) )) < 1

thanks for sparking an interesting discussion.

[Joshua Berry]

On Mon, Nov 2, 2009 at 8:35 AM, Jasen Betts <***@xnet.co.nz> wrote:
|
| for this case: convert to seconds and then do abs.
|
|  select * from enviados e, recibidos r where abs(extract ( epoch from
|  (e.fecha - r.fecha) )) < 1

Cheers for that. The query cost is pretty heavy, but the same as the
following query which involves two WHERE sections:

| select * from enviados e, recibidos r where (e.fecha - r.fecha) <
| interval '1 second' AND (r.fecha - e.fecha) < interval '1 second'

But both have a higher cost than the following query with "OVERLAPS"

| select * from enviados e, recibidos r where (r.fecha + interval '1
| seconds', r.fecha - interval '1 seconds') OVERLAPS (e.fecha, e.fecha);

We're only talking about a 4% difference on estimated costs in the
billions, but it made me wonder if I should be using OVERLAPS more
regularly as it may be more highly optimized.

| thanks for sparking an interesting discussion.

Thanks. I feel a bit humbled by thinking that I could help add an
operator that didn't exist simply because no one had gotten around too
it! I realize now that it's a lot more complex of an issue than I
thought it was.

[Bruce Momjian]

Post by Joshua Berry
|
| for this case: convert to seconds and then do abs.
|
| ?select * from enviados e, recibidos r where abs(extract ( epoch from
| ?(e.fecha - r.fecha) )) < 1
Cheers for that. The query cost is pretty heavy, but the same as the
| select * from enviados e, recibidos r where (e.fecha - r.fecha) <
| interval '1 second' AND (r.fecha - e.fecha) < interval '1 second'
But both have a higher cost than the following query with "OVERLAPS"
| select * from enviados e, recibidos r where (r.fecha + interval '1
| seconds', r.fecha - interval '1 seconds') OVERLAPS (e.fecha, e.fecha);
We're only talking about a 4% difference on estimated costs in the
billions, but it made me wonder if I should be using OVERLAPS more
regularly as it may be more highly optimized.
| thanks for sparking an interesting discussion.
Thanks. I feel a bit humbled by thinking that I could help add an
operator that didn't exist simply because no one had gotten around too
it! I realize now that it's a lot more complex of an issue than I
thought it was.

I have added a C comment the references this discussion so we remember
why the function does not exist:

Add C comment about why there is no interval_abs(): it is unclear what
value to return:

http://archives.postgresql.org/pgsql-general/2009-10/msg01031.php
http://archives.postgresql.org/pgsql-general/2009-11/msg00041.php
--
Bruce Momjian <***@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

[Jasen Betts]

Post by Joshua Berry
|
| for this case: convert to seconds and then do abs.
|
|  select * from enviados e, recibidos r where abs(extract ( epoch from
|  (e.fecha - r.fecha) )) < 1
Cheers for that. The query cost is pretty heavy, but the same as the

indeed, this one should be more efficient but is not exactly identical.

select * from enviados e, recibidos r where e.fecha between r.fecha
-'1s' and r.fecha +'1s';

the difference being that the above includes the case where the
difference is exactly 1 second.

this might work better:

select * from enviados e, recibidos r where e.fecha between r.fecha
-'0.999999s' and r.fecha +'0.999999s';

it depends how critical the edge cases are.