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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
