Обсуждение: Re: doverlaps() returns null

On May 20, 1:39 pm, "Andrus" <kobrule...@hot.ee> wrote:
> I need to check when two date intervals overlap.
> Some date interval values may be null.
>
> I created the following function but
>
> select doverlaps( null, null, null, null);
>
> returns null.
>
> How to fix this so that null values are allowed in parameters ?
>
> Andrus.
>
> -- returns true when date interval $1 .. $2  overlaps with $3 .. $4
> -- null values are allowed in parameters.
> CREATE OR REPLACE FUNCTION public.doverlaps(date,
>         date, date, date, out bool) immutable AS
> $_$
> SELECT coalesce($1, timestamp '-infinity'::date)  <=coalesce($4, timestamp
> 'infinity'::date) AND
>      coalesce($2, timestamp 'infinity'::date)>=coalesce($3, timestamp
> '-infinity'::date);
> $_$ language sql;

You cannot cast +/- infinity timestamp to date, but you can cast date
to timestamp.

And what's wrong with OVERLAPS? e.g. :

CREATE OR REPLACE FUNCTION
PUBLIC.DOVERLAPS
(DATE, DATE, DATE, DATE, OUT BOOL)
IMMUTABLE AS
$_$
SELECT
(COALESCE($1::TIMESTAMP, TIMESTAMP '-INFINITY')
 , COALESCE($2::TIMESTAMP, TIMESTAMP 'INFINITY')
 )
OVERLAPS(
         COALESCE($3::TIMESTAMP, TIMESTAMP '-INFINITY')
         , COALESCE($4::TIMESTAMP, TIMESTAMP 'INFINITY'));
$_$ LANGUAGE SQL;

t=# SELECT doverlaps( NULL, NULL, NULL, NULL);
 doverlaps
-----------
 t

On May 21, 4:49 am, "Andrus" <kobrule...@hot.ee> wrote:
> I'm using this for emplyment, vacation, illness etc. period calculation.
> OVERLAPS produces invalid result in this case for DATE as discussed in other
> thread.
>
> select doverlaps(date '2007-01-01',date '2007-01-02',date '2007-01-02',date
> '2007-01-04');
>
> returns  FALSE
>
> When first period end and second period start dates are the the same,
> doverlaps() must return TRUE.
>
> Andrus.

OR $2 = $3

> You cannot cast +/- infinity timestamp to date

Thank you.

All my dates are in nearest centuries. So I fixed this by creating function

CREATE OR REPLACE FUNCTION public.doverlaps(date,date, date, date, out bool)
immutable AS
$_$
SELECT coalesce($1, date '0001-01-01')  <=coalesce($4, date '9999-12-31')
AND
     coalesce($2, date '9999-12-31')>=coalesce($3, date '0001-01-01' );
$_$ language sql;


is this best solution ?

> , but you can cast date to timestamp.
> And what's wrong with OVERLAPS? e.g. :
>
> CREATE OR REPLACE FUNCTION
> PUBLIC.DOVERLAPS
> (DATE, DATE, DATE, DATE, OUT BOOL)
> IMMUTABLE AS
> $_$
> SELECT
> (COALESCE($1::TIMESTAMP, TIMESTAMP '-INFINITY')
> , COALESCE($2::TIMESTAMP, TIMESTAMP 'INFINITY')
> )
> OVERLAPS(
>         COALESCE($3::TIMESTAMP, TIMESTAMP '-INFINITY')
>         , COALESCE($4::TIMESTAMP, TIMESTAMP 'INFINITY'));
> $_$ LANGUAGE SQL;
>
> t=# SELECT doverlaps( NULL, NULL, NULL, NULL);
> doverlaps
> -----------
> t

I'm using this for emplyment, vacation, illness etc. period calculation.
OVERLAPS produces invalid result in this case for DATE as discussed in other
thread.

select doverlaps(date '2007-01-01',date '2007-01-02',date '2007-01-02',date
'2007-01-04');

returns  FALSE

When first period end and second period start dates are the the same,
doverlaps() must return TRUE.

Andrus.