Absolute value of intervals

From: Joshua Berry <yoberi(at)gmail(dot)com>
To: PostgreSQL - General <pgsql-general(at)postgresql(dot)org>
Subject: Absolute value of intervals
Date: 2009-10-27 14:27:17
Message-ID: 5ccd53c10910270727m5bf6d4adoa9424f49a397ca5e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

I couldn't find the operator '@' for intervals and found this thread
from over six years ago:

| "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

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.

-Joshua Berry


Browse pgsql-general by date

  From Date Subject
Next Message Raimon Fernandez 2009-10-27 14:43:20 Re: Implementing Frontend/Backend Protocol TCP/IP
Previous Message JC Praud 2009-10-27 14:15:22 auto truncate/vacuum full