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 li
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 sys
> 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 expe
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 clo
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 sam
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
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'200
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'::timesta
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 in
> 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
> enterprete
> > 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 enter
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
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.
> 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 kn
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 'i
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'::
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 qu
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 NUL
> 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 repr
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
"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 s
> 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
"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 writi
>> 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 -- 200
"Andrus" <[EMAIL PROTECTED]> 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, '-12-31'::date +1) OVERLAPS
>>('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)
>
> Alban,
>
> thank you. I use on
> 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
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 t
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
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 tw
> 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
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)-
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
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 whet
L 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'::d
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)---
35 matches
Mail list logo
