daterange_inclusive is released under a PostgreSQL-like License, a liberal Open Source license, similar to the BSD or MIT licenses.

Portions Copyright (c) 2025, Postgres Professional

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL POSTGRES PROFESSIONAL BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF POSTGRES PROFESSIONAL HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

POSTGRES PROFESSIONAL SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND POSTGRES PROFESSIONAL HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

EXTENSION=daterange_inclusive

MODULE_big=daterange_inclusive

OBJS = daterange_inclusive.o

PGFILEDESC = "daterange_inclusive - daterange with inclusive upper bound"

DATA = "daterange_inclusive--1.0.sql"

REGRESS="daterange_inclusive"

PGXS :=$(shell$(PG_CONFIG) --pgxs)

A datarange type with inclusive upper bound

A PostgreSQL datarange type with inclusive upper bound

The daterange type, as well as other range types, is by default output with non-exclusive upper range.

This extension defines a type which is the same but with inclusive upper range.

Such behaviour is more convenient in some practical use cases.

See example:

CREATETYPEdaterange_inclusive;

CREATEFUNCTIONdate_minus(date1date, date2date) RETURNS floatAS $$

SELECT cast(date1- date2AS float);

$$ LANGUAGE sql IMMUTABLE;

CREATEFUNCTIONdaterange_inclusive_canonical(dr daterange_inclusive) RETURNS daterange_inclusiveAS'MODULE_PATHNAME'

LANGUAGE C STRICT IMMUTABLE;

CREATETYPEdaterange_inclusiveAS RANGE (

SUBTYPE=date,

SUBTYPE_DIFF= date_minus,

CANONICAL= daterange_inclusive_canonical

);

CREATE CAST (daterangeAS daterange_inclusive) WITH INOUTAS IMPLICIT;

CREATE CAST (daterange_inclusiveAS daterange) WITH INOUTAS IMPLICIT;

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(daterange_inclusive_canonical);

daterange_inclusive_canonical(PG_FUNCTION_ARGS)

{

RangeType*r=PG_GETARG_RANGE_P(0);

Node*escontext=fcinfo->context;

TypeCacheEntry*typcache;

RangeBoundlower;

RangeBoundupper;

boolempty;

typcache=range_get_typcache(fcinfo,RangeTypeGetOid(r));

range_deserialize(typcache,r,&lower,&upper,&empty);

if (empty)

PG_RETURN_RANGE_P(r);

if (!lower.infinite&& !DATE_NOT_FINITE(DatumGetDateADT(lower.val))&&

!lower.inclusive)

{

DateADTbnd=DatumGetDateADT(lower.val);

bnd++;

if (unlikely(!IS_VALID_DATE(bnd)))

ereturn(escontext, (Datum)0,

(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),

errmsg("date out of range")));

lower.val=DateADTGetDatum(bnd);

lower.inclusive= true;

}

if (!upper.infinite&& !DATE_NOT_FINITE(DatumGetDateADT(upper.val))&&

!upper.inclusive)

{

DateADTbnd=DatumGetDateADT(upper.val);

bnd--;

if (unlikely(!IS_VALID_DATE(bnd)))

ereturn(escontext, (Datum)0,

(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),

errmsg("date out of range")));

upper.val=DateADTGetDatum(bnd);

upper.inclusive= true;

}

PG_RETURN_RANGE_P(range_serialize(typcache,&lower,&upper,

false,escontext));

}

# daterange_inclusive extension

comment = 'daterange with inclusive upper bound'

default_version = '1.0'

module_pathname = '$libdir/daterange_inclusive'

relocatable = true

trusted = true

CREATE EXTENSION daterange_inclusive;

SELECT daterange_inclusive('2024-01-01' , '2024-06-01', '[]');

daterange_inclusive

-------------------------

[01-01-2024,06-01-2024]

(1 row)

SELECT daterange_inclusive('2024-01-01' , '2024-06-01', '[)');

daterange_inclusive

-------------------------

[01-01-2024,05-31-2024]

(1 row)

SELECT '[2025-01-01,2025-02-02]'::daterange::daterange_inclusive;

daterange_inclusive

-------------------------

[01-01-2025,02-02-2025]

(1 row)

SELECT '[2025-01-01,2025-02-02)'::daterange::daterange_inclusive;

daterange_inclusive

-------------------------

[01-01-2025,02-01-2025]

(1 row)

SELECT '[2025-01-01,2025-02-02]'::daterange_inclusive::daterange;

daterange

-------------------------

[01-01-2025,02-03-2025)

(1 row)

SELECT '[2025-01-01,2025-02-02)'::daterange_inclusive::daterange;

daterange

-------------------------

[01-01-2025,02-02-2025)

(1 row)

CREATE TABLE x (y daterange_inclusive);

INSERT INTO x SELECT daterange_inclusive(d::date, (d + '1month'::interval)::date, '[]') FROM generate_series('2015-01-01'::date,'2025-02-02', '1day') d;

INSERT INTO x SELECT daterange_inclusive(d::date, NULL::date, '[)') FROM generate_series('2015-01-01'::date,'2025-02-02', '1day') d;

SELECT COUNT(*) FROM x;

count

-------

7372

(1 row)

CREATE INDEX ON x USING gist (y);

INSERT INTO x SELECT '[2024-01-01,)'::daterange::daterange_inclusive;

DROP EXTENSION daterange_inclusive CASCADE;

NOTICE: drop cascades to column y of table x

CREATE EXTENSION daterange_inclusive;

SELECT daterange_inclusive('2024-01-01' ,'2024-06-01','[]');

SELECT daterange_inclusive('2024-01-01' ,'2024-06-01','[)');

SELECT'[2025-01-01,2025-02-02]'::daterange::daterange_inclusive;

SELECT'[2025-01-01,2025-02-02)'::daterange::daterange_inclusive;

SELECT'[2025-01-01,2025-02-02]'::daterange_inclusive::daterange;

SELECT'[2025-01-01,2025-02-02)'::daterange_inclusive::daterange;

CREATETABLEx (y daterange_inclusive);

INSERT INTO xSELECT daterange_inclusive(d::date, (d+'1month'::interval)::date,'[]')FROM generate_series('2015-01-01'::date,'2025-02-02','1day') d;

INSERT INTO xSELECT daterange_inclusive(d::date,NULL::date,'[)')FROM generate_series('2015-01-01'::date,'2025-02-02','1day') d;

SELECTCOUNT(*)FROM x;

CREATEINDEXON x USING gist (y);

INSERT INTO xSELECT'[2024-01-01,)'::daterange::daterange_inclusive;

DROP EXTENSION daterange_inclusive CASCADE;