Re: Why overlaps is not working

Andrus wrote:
>> I thought the suggested solution was to use infinity, hence the
>> requirement to cast to timestamps.
>> That'd mean something along the lines of:
>>
>>  where  (a::timestamp, coalesce(b, 'infinity')::timestamp) overlaps
>>  (c::timestamp, coalesce(d, 'infinity')::timestamp)
>
> select   (date'20060101'::timestamp, coalesce(date'20060102'::timestamp,
> 'infinity')) overlaps
>  (date'20060102', coalesce(date'20060103'::timestamp, 'infinity'))
>
> returns false but since date'20060102' is overlapping it must return true.
> So it seems that it is not possible to use timestamps and infinity.

Not true, as the above query reads:

  select (date'20060101'::timestamp, date'20060102'::timestamp)
  overlaps
  (date'20060102', date'20060103'::timestamp)

Which doesn't overlap.

What you meant to test is:

  select (date '20060101'::timestamp,
     coalesce(NULL, 'infinity'::timestamp))
  overlaps
  (date '20060102'::timestamp,
     coalesce(NULL, 'infinity'::timestamp))

Which returns true.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //