Re: [GENERAL] Why overlaps is not working
Jim Nasby wrote: I know that numeric supports +/- infinity; I don't remember off-hand if timestamps have that as well. timestamps do, but dates don't. -- Alban Hertroys [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
On Nov 23, 2006, at 10:57 AM, Richard Broersma Jr wrote: If my database contains dates greater than DATE '-12-31' then this check fails. This is why I'm searching for a real MAX_DATE value in Postgres. It would be nice if there will be MAX_DATE constant in Postgres or some one row system table contains MAX_DATE value. through expermentation I came up with: logs=# select '5874897-12-31'::date; date --- 5874897-12-31 (1 row) logs=# select '5874898-12-31'::date; ERROR: date out of range: 5874898-12-31 Keep in mind that that number could change depending on if you're using integer or floating point timestamps. I know that numeric supports +/- infinity; I don't remember off-hand if timestamps have that as well. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why overlaps is not working
If my database contains dates greater than DATE '-12-31' then this check fails. This is why I'm searching for a real MAX_DATE value in Postgres. It would be nice if there will be MAX_DATE constant in Postgres or some one row system table contains MAX_DATE value. through expermentation I came up with: logs=# select '5874897-12-31'::date; date --- 5874897-12-31 (1 row) logs=# select '5874898-12-31'::date; ERROR: date out of range: 5874898-12-31 Regards, Richard Broersma Jr l ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why overlaps is not working
Andrus wrote: If he casts all his dates to timestamps then this might be a good option. Thank you. where (a::timestamp, coalesce(b, '1231')::timestamp) overlaps (c::timestamp, coalesce(d, '1231')::timestamp) would be simplest solution. 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) -- Alban Hertroys [EMAIL PROTECTED] 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 // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why overlaps is not working
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. Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 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 [EMAIL PROTECTED] 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 // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
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, If first period end and second period start dates are the same, I need that in this case expression returns true. Is it possible to implement this using OVERLAPS operator ? Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Overlap flags (Was: Re: [GENERAL] Why overlaps is not working)
Andrus wrote: 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, If first period end and second period start dates are the same, I need that in this case expression returns true. Is it possible to implement this using OVERLAPS operator ? You could probably adjust your dates to make OVERLAPS return true, or you could use the recently suggested way using two BETWEEN statements, or you could write your own exclusive OVERLAPS operator. As a proper solution, but that requires changing PostgreSQL: Maybe it is an idea to implement an additional 'flag' to OVERLAPS and BETWEEN that tells whether the areas to test should be compared INCLUSIVE or EXCLUSIVE? I'd imagine something like this. SELECT (date '20060101', date '20060630') OVERLAPS (date '20060630', date '20061231') EXCLUSIVE -- The current/default behaviour --- f SELECT (date '20060101', date '20060630') OVERLAPS (date '20060630', date '20061231') INCLUSIVE --- t And using BETWEEN: SELECT date '20060101' BETWEEN date '20060101' AND date '20060630' EXCLUSIVE --- f SELECT date '20060101' BETWEEN date '20060101' AND date '20060630' INCLUSIVE -- The current/default behaviour --- t This reasoning would be valid for any operator working on at least one range of values. Next to that, the defaults of OVERLAPS and BETWEEN behaviour being different may need fixing too. Although I realise that this would break existing implementations, so maybe that's a bad idea. I suppose the SQL standard specifies the behaviour of these operators, but adding an optional flag doesn't seem to break compliance. Is this acceptable? -- Alban Hertroys [EMAIL PROTECTED] 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 // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why overlaps is not working
If first period end and second period start dates are the same, I need that in
this case expression
returns true.
Is it possible to implement this using OVERLAPS operator ?
I think the best workaround is a function of some kind in whichever
language you choose. I think you could actually clobber overlaps()
but I chose to give mine a different name. In my world, all date
ranges have a start, but can have an indefinite end (null).
CREATE OR REPLACE FUNCTION isoverlap (date,date,date,date) RETURNS
boolean LANGUAGE pltcl AS '
set d1 [clock scan $1]
set d3 [clock scan $3]
if {[string length $2] == 0} {
set d2 0
} else {
set d2 [clock scan $2]
}
if {[string length $4] == 0} {
set d4 0
} else {
set d4 [clock scan $4]
}
if {($d2 = $d3 ($d1 = $d4 || !$d4)) ||
($d1 = $d4 ($d2 = $d3 || !$d2)) ||
(!$d2 !$d4)} {
return true
} else {
return false
}
' ;
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Why overlaps is not working
apparently date doesn't know anything about infinity. However, from what I've read in my SQL for smarties book regarding temporial database design, unknown future dates were stored as: '-12-31' Would this help, since any enddate with this value would be be enterpreted as an enddate that has not yet occured? when you arrive at the date for records effective period to close just update the enddate to the today's date. select date '1-1-1' date '1231' return false. If my database contains dates greater than DATE '-12-31' then this check fails. This is why I'm searching for a real MAX_DATE value in Postgres. It would be nice if there will be MAX_DATE constant in Postgres or some one row system table contains MAX_DATE value. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why overlaps is not working
apparently date doesn't know anything about infinity. However, from what I've read in my SQL for smarties book regarding temporial database design, unknown future dates were stored as: '-12-31' Would this help, since any enddate with this value would be be enterpreted as an enddate that has not yet occured? when you arrive at the date for records effective period to close just update the enddate to the today's date. select date '1-1-1' date '1231' return false. If my database contains dates greater than DATE '-12-31' then this check fails. This is why I'm searching for a real MAX_DATE value in Postgres. It would be nice if there will be MAX_DATE constant in Postgres or some one row system table contains MAX_DATE value. That is very interesting, but would you really expect to record dates greater than the year ? :o) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why overlaps is not working
apparently date doesn't know anything about infinity. However, from what I've read in my SQL for smarties book regarding temporial database design, unknown future dates were stored as: '-12-31' Would this help, since any enddate with this value would be be enterpreted as an enddate that has not yet occured? when you arrive at the date for records effective period to close just update the enddate to the today's date. select date '1-1-1' date '1231' return false. If my database contains dates greater than DATE '-12-31' then this check fails. This is why I'm searching for a real MAX_DATE value in Postgres. It would be nice if there will be MAX_DATE constant in Postgres or some one row system table contains MAX_DATE value. That is very interesting, but would you really expect to record dates greater than the year ? Some programmer who did'nt read the book you mentioned but some other sql book may use date '10001-1-1' for marking infinity. So this will break by code. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why overlaps is not working
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, '1231')) or (coalesce($4, '1231') between $1 and coalesce($2, '1231')); $_$ language sql; It this best solution ? How many times this is slower than expression in where clause? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
Andrus [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why overlaps is not working
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
Richard Broersma Jr [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why overlaps is not working
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. My table represents employee absence starting and ending dates. If end day is not yet known, it is represented by NULL value. My query should threat unknown value as never ending absence to return estimated number of work days. Infinity date value is missing in SQL standard. I do'nt know any other good way to represent missing ending date. There is a value infinity for timestamps, but unfortunately not for dates. Otherwise, I'd suggest that you use that instead. I tried to use timestamp 'infinity':: date but this does not work if both b and d are infinity since select timestamp 'infinity':: date=timestamp 'infinity':: date returns null. Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why overlaps is not working
If he casts all his dates to timestamps then this might be a good option. Thank you. where (a::timestamp, coalesce(b, '1231')::timestamp) overlaps (c::timestamp, coalesce(d, '1231')::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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
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 '93112' 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why overlaps is not working
My table represents employee absence starting and ending dates. If end day is not yet known, it is represented by NULL value. My query should threat unknown value as never ending absence to return estimated number of work days. Infinity date value is missing in SQL standard. I do'nt know any other good way to represent missing ending date. There is a value infinity for timestamps, but unfortunately not for dates. Otherwise, I'd suggest that you use that instead. I tried to use timestamp 'infinity':: date but this does not work if both b and d are infinity since select timestamp 'infinity':: date=timestamp 'infinity':: date returns null. This might explain why you are getting null; logs=# select 'infinity'::date; ERROR: invalid input syntax for type date: infinity logs=# select 'infinity'::timestamp; timestamp --- infinity (1 row) apparently date doesn't know anything about infinity. However, from what I've read in my SQL for smarties book regarding temporial database design, unknown future dates were stored as: '-12-31' Would this help, since any enddate with this value would be be enterpreted as an enddate that has not yet occured? when you arrive at the date for records effective period to close just update the enddate to the today's date. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
Richard Broersma Jr [EMAIL PROTECTED] writes: apparently date doesn't know anything about infinity. It doesn't, but we have a TODO item to make it do so, which would presumably include making the timestamp-to-date cast do something more sensible with an infinity timestamp. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Why overlaps is not working
set datestyle to iso,iso;
select 1 where ('2006-10-31'::date, '-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
Re: [GENERAL] Why overlaps is not working
Hm, why not this one:
select ('2006-10-31'::date, '-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);
overlaps
--
f
(1 row)
Greetings,
Matthias
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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, '-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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Why overlaps is not working
Andrus wrote:
set datestyle to iso,iso;
select 1 where ('2006-10-31'::date, '-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, '-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);
?column?
--
1
(1 row)
--
Alban Hertroys
[EMAIL PROTECTED]
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 //
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why overlaps is not working
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, '-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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
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, '-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.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Why overlaps is not working
2006/11/9, Andrus [EMAIL PROTECTED]:
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 theirendpoints) overlap How to make overlaps to return correct result?select 1 where ('2006-10-30'::date, '-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);In real queries I have column names and parameters instead of dataconstants.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 '-12-31'::date) OR ('2006-10-31'::DATE BETWEEN '2006-10-30'::date AND '-12-31'::date)
-- William Leite Araújo
Re: [GENERAL] Why overlaps is not working
Alban Hertroys [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
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, '-12-31'::date +1) OVERLAPS
('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)
Regards,
--
Alban Hertroys
[EMAIL PROTECTED]
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 //
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
Why not just subtract/add 1, so that the check includes the boundary
dates?
Like so;
select 1 where ('2006-10-31'::date -1, '-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.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
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 AND2006-12-06 -- 2006-12-15 (original) -- FALSE 2006-11-29 -- 2006-12-06 AND2006-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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
Andrus [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
