Why overlaps is not working

Hm, why not this one:

select ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);

overlaps
----------
f
(1 row)

Greetings,
Matthias

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrus
Sent: Thursday, November 09, 2006 2:47 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Why overlaps is not working

set datestyle to iso,iso;

select 1 where ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE)

does not return any rows.

Why ?
How to make overlaps to return correct result?

Andrus.

---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andrus wrote:

set datestyle to iso,iso;

select 1 where ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE)

does not return any rows.

Why ?

They're adjacent, they don't overlap. Check the documentation on
OVERLAPS, I'm sure it's explicit about whether it is inclusive or
exclusive (the latter apparently).

How to make overlaps to return correct result?

select 1 where ('2006-10-30'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);
?column?
----------
1
(1 row)

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

I have a number of select statements (in 8.1 and 8.2beta) which assume that
overlaps returns true for those cases.

Which the best way to fix them ?

Should I use AND, OR and date comparison operators instead of OVERLAPS ?

Andrus.

am Thu, dem 09.11.2006, um 15:46:50 +0200 mailte Andrus folgendes:

set datestyle to iso,iso;

select 1 where ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE)

does not return any rows.

Why ?
How to make overlaps to return correct result?

Because they don't overlaps.

Example:

test=*# select ('2006-10-01'::date, '2006-10-11'::date) OVERLAPS ('2006-10-11'::DATE, '2006-10-20'::DATE);
overlaps
----------
f
(1 row)

test=*# select ('2006-10-01'::date, '2006-10-12'::date) OVERLAPS ('2006-10-11'::DATE, '2006-10-20'::DATE);
overlaps
----------
t
(1 row)

Your date-range don't overlap, because the 2nd ends '2006-10-31' and the other
begin with '2006-10-31'. And your query can't return anything because the where-condition
returns false.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

They're adjacent, they don't overlap. Check the documentation on OVERLAPS,
I'm sure it's explicit about whether it is inclusive or exclusive (the
latter apparently).

8.2 doc does not explain term overlap. It only says:

"This expression yields true when two time periods (defined by their
endpoints) overlap"

How to make overlaps to return correct result?

select 1 where ('2006-10-30'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);

In real queries I have column names and parameters instead of data
constants.
The only way it seems to replace OVERLAPS operator with AND, OR, <=
operators.

Is it so ?

Andrus.

2006/11/9, Andrus <eetasoft@online.ee>:

They're adjacent, they don't overlap. Check the documentation on

OVERLAPS,

I'm sure it's explicit about whether it is inclusive or exclusive (the
latter apparently).

8.2 doc does not explain term overlap. It only says:

"This expression yields true when two time periods (defined by their
endpoints) overlap"

How to make overlaps to return correct result?

select 1 where ('2006-10-30'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);

In real queries I have column names and parameters instead of data
constants.
The only way it seems to replace OVERLAPS operator with AND, OR, <=
operators.

Is it so ?

Andrus.

Maybe:
('2006-10-16'::DATE BETWEEN '2006-10-30'::date AND '9999-12-31'::date)
OR
('2006-10-31'::DATE BETWEEN '2006-10-30'::date AND '9999-12-31'::date)

--
William Leite Araújo

Alban Hertroys <alban@magproductions.nl> writes:

They're adjacent, they don't overlap. Check the documentation on
OVERLAPS, I'm sure it's explicit about whether it is inclusive or
exclusive (the latter apparently).

It's not very clear, but the spec defines (S1,T1) OVERLAPS (S2,T2)
as

( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
OR
( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
OR
( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )

(for the simple case where there are no nulls and S1 <= T1, S2 <= T2).
So it looks to me like the intervals are actually considered to be
half-open intervals [S1, T1). Which is something that has its uses,
but it's a bit surprising compared to, say, BETWEEN.

If you don't like it, write your own comparison function ...

regards, tom lane

Andrus wrote:

I have a number of select statements (in 8.1 and 8.2beta) which assume that
overlaps returns true for those cases.

Which the best way to fix them ?

Should I use AND, OR and date comparison operators instead of OVERLAPS ?

Why not just subtract/add 1, so that the check includes the boundary dates?

Like so;
select 1 where ('2006-10-31'::date -1, '9999-12-31'::date +1) OVERLAPS
('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)

Regards,
--
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 //

Why not just subtract/add 1, so that the check includes the boundary
dates?

Like so;
select 1 where ('2006-10-31'::date -1, '9999-12-31'::date +1) OVERLAPS
('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)

Alban,

thank you. I use only dates as OVERLAPS arguments.
I changed all my WHERE clauses from

WHERE (a,b) OVERLAPS (c,d)

to

WHERE (a-1,b+1) OVERLAPS (c-1,d+1)

Will this give correct results ?

Andrus.

"Andrus" <eetasoft@online.ee> writes:

Why not just subtract/add 1, so that the check includes the boundary
dates?

Like so;
select 1 where ('2006-10-31'::date -1, '9999-12-31'::date +1) OVERLAPS
('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)

Alban,

thank you. I use only dates as OVERLAPS arguments.
I changed all my WHERE clauses from

WHERE (a,b) OVERLAPS (c,d)

to

WHERE (a-1,b+1) OVERLAPS (c-1,d+1)

Will this give correct results ?

It might give you false positives...

2006-11-30 -- 2006-12-05 AND 2006-12-06 -- 2006-12-15 (original) -- FALSE
2006-11-29 -- 2006-12-06 AND 2006-12-05 -- 2006-12-16 (changed) -- TRUE

Be seeing you,
--
Jorge Godoy <jgodoy@gmail.com>

WHERE (a,b) OVERLAPS (c,d)

to

WHERE (a-1,b+1) OVERLAPS (c-1,d+1)

Will this give correct results ?

It might give you false positives...

2006-11-30 -- 2006-12-05 AND 2006-12-06 -- 2006-12-15 (original) --
FALSE
2006-11-29 -- 2006-12-06 AND 2006-12-05 -- 2006-12-16 (changed) --
TRUE

Jorge,

Thank you very much. Now I try to William Leite Ara�jo solution by replacing

WHERE (a,b) OVERLAPS (c,d)

with

WHERE ( c BETWEEN a AND b ) OR ( d BETWEEN a AND b )

Is this OK ?
This requires writing a and b expressions twice. How to avoid repeating
expressions ?

Andrus.

"Andrus" <eetasoft@online.ee> writes:

Jorge,

Thank you very much. Now I try to William Leite Araújo solution by replacing

WHERE (a,b) OVERLAPS (c,d)

with

WHERE ( c BETWEEN a AND b ) OR ( d BETWEEN a AND b )

Is this OK ?

From bare tests this looks OK.

This requires writing a and b expressions twice. How to avoid repeating
expressions ?

You can use a function for that and use variables for the four arguments:

CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date,
date, date, date, out overlaps bool) as
$_$
SELECT (($3 between $1 and $2) or ($4 between $1 and $2));
$_$ language sql;

Be seeing you,
--
Jorge Godoy <jgodoy@gmail.com>

CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date,
date, date, date, out overlaps bool) as
$_$
SELECT (($3 between $1 and $2) or ($4 between $1 and $2));
$_$ language sql;

Thank you.
In my application second and fourth parameters can be NULL which means
forever.
So I tried the code:

CREATE OR REPLACE FUNCTION public.doverlaps(date,
date, date, date, out bool) IMMUTABLE AS
$_$
IF $1 is NULL OR $3 IS NULL THEN
RAISE EXCEPTION 'doverlaps: first or third parameter is NULL % %',$1,$3;
END IF;

IF $2 is null and $4 is null THEN
SELECT true;
RETURN;
END IF;

IF $2 is null THEN
SELECT $1<=$4;
RETURN;
END IF;

IF $4 is null THEN
SELECT $2>=$3;
RETURN;
END IF;

SELECT ($3 between $1 and $2) or ($4 between $1 and $2);

$_$ language sql;

This causes error

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 109

So I changed code to

CREATE OR REPLACE FUNCTION public.doverlaps(date,
date, date, date, out bool) IMMUTABLE AS
$_$
SELECT ($3 between $1 and coalesce($2, '99991231')) or
(coalesce($4, '99991231') between $1 and coalesce($2, '99991231'));
$_$ language sql;

It this best solution ?
How many times this is slower than expression in where clause?

Andrus.

"Andrus" <eetasoft@online.ee> writes:

This causes error

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 109

SQL has no "IF". Use plpgsql instead.

How many times this is slower than expression in where clause?

You can time it. :-) But I don't believe it will be too slow since it is a
simple operation...

--
Jorge Godoy <jgodoy@gmail.com>

Andrus wrote:

CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date,
date, date, date, out overlaps bool) as
$_$
SELECT (($3 between $1 and $2) or ($4 between $1 and $2));
$_$ language sql;

Thank you.
In my application second and fourth parameters can be NULL which means
forever.

No it doesn't. NULL means "unknown". You're just using it to represent
"forever".

There is a value "infinity" for timestamps, but unfortunately not for
dates. Otherwise, I'd suggest that you use that instead.

--
Richard Huxton
Archonet Ltd

In my application second and fourth parameters can be NULL which means
forever.
It this best solution ?
How many times this is slower than expression in where clause?

I am not sure if this would work for you, but instead of using NULL to represent infinity, why not
use 'infinity' to represent infinity?

logs=# select 'infinity'::timestamp;
timestamp
-----------
infinity
(1 row)

Regards,

Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:

In my application second and fourth parameters can be NULL which means
forever.
It this best solution ?
How many times this is slower than expression in where clause?

I am not sure if this would work for you, but instead of using NULL to
represent infinity, why not use 'infinity' to represent infinity?

If he casts all his dates to timestamps then this might be a good option.

--
Jorge Godoy <jgodoy@gmail.com>

If he casts all his dates to timestamps then this might be a good option.

Thank you.

where (a::timestamp, coalesce(b, '99991231')::timestamp) overlaps
(c::timestamp, coalesce(d, '99991231')::timestamp)

would be simplest solution.

However

select (date '20050101'::timestamp, date '20060101'::timestamp) overlaps
(date '20060101'::timestamp, date '20070101'::timestamp)

returns false

So this cannot used for date overlapping.
Which sytax to use to substract/add a minute to make this correct?

Andrus.

I am not sure if this would work for you, but instead of using NULL to
represent infinity, why not
use 'infinity' to represent infinity?

Infinity dehaves differenty than ordinal dates and nulls.
If both b and d are infinity then comparison fails:

select timestamp 'infinity':: date<=timestamp 'infinity':: date

returns null.

So infinity introduces third kind of FUD in addition to usual date and
null comparisons. NULLs in SQL are disaster. With infinity SQL is double
disaster.

In samples I used DATE '999993112' but this is incorrect.
I must use maximum allowed date or max_timestamp casted to date.
Is it reasonable to use it ?
I hope that MAX_DATE <= MAX_DATE returns true.
Which is the value of MAX_DATE is Postgres ?

Andrus.