Thread: A Nit RE overlaps

I love overlaps() but I would love it more if it had a

overlaps(date, date, date, date) variant that would work like this:

select overlaps(
    cast('01/01/2002' as date),
    cast('6/1/2002' as date),
    cast('6/1/2002' as date),
    cast('12/31/2002' as date)
);
  overlaps
----------
 t
(1 row)

As it is, it returns false.  To me if the first interval ends at a point in time and the other begins at the same
point,they overlap.  This is splitting hairs with a timestamp, but not with a date.  Am I the only person who feels
thisway?  Is there a way to use overlaps as above and make it return true? 

In my twisted little world, a null date would be OK too and would indicate the beginning or end of time.  i.e.

select overlaps(
    cast('01/01/2002' as date),
    cast(null as date),
    cast('6/1/2002' as date),
    cast('12/31/2002' as date)
);

would return

  overlaps
----------
 t
(1 row)

since the first interval goes on forever.  I know that is a bad use of nulls in terms of its meaning as 'unknown' so I
guessthat's a hack I will have to continue doing myself.  But I am still interested in knowing if there is any interest
ina version of overlaps that considers events that occur at the same point in time to be overlapping. 



Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.org

"Ian Harding" <ianh@tpchd.org> writes:
> As it is, it returns false.  To me if the first interval ends at a
> point in time and the other begins at the same point, they overlap.
> This is splitting hairs with a timestamp, but not with a date.  Am I
> the only person who feels this way?

SQL92 says that abutting time intervals don't overlap, if I'm reading
the spec correctly.

For dates, it would seem to me that you want to add 24 hours to the end
of one interval in order to get the behavior you're describing.

> In my twisted little world, a null date would be OK too and would
> indicate the beginning or end of time.

I agree, that's pretty bogus.  Why not use infinity and -infinity?
(okay, they don't exist as DATE values, but they do exist as
timestamp values).

            regards, tom lane