Re: Absolute value of intervals

On Mon, Nov 2, 2009 at 8:35 AM, Jasen Betts <jasen(at)xnet(dot)co(dot)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.