From: | Rodrigo De León <rdeleonp(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: doverlaps() returns null |
Date: | 2007-05-21 07:13:10 |
Message-ID: | 1179731590.842977.267030@r3g2000prh.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On May 20, 1:39 pm, "Andrus" <kobrule(dot)(dot)(dot)(at)hot(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2007-05-21 07:53:20 | Re: How to implement GOMONTH function |
Previous Message | Albe Laurenz | 2007-05-21 06:38:34 | Re: Random Sample |