Postgresql with daterange on the same day

Question

I'm trying to create a virtual column called period created by 2 other columns starting_date (time) and ending_date (time).

The aim is to avoid overlapping of periods for the same user.

Here is my current SQL

CREATE TABLE public.classrooms (
 id bigint generated by default as identity primary key,
 levels character varying[] DEFAULT '{}'::character varying[] NOT NULL,
 year integer NOT NULL,
 created_at timestamp(6) without time zone NOT NULL,
 updated_at timestamp(6) without time zone NOT NULL
);
CREATE TABLE public.absences (
 id bigint generated by default as identity primary key,
 classroom_id bigint NOT NULL,
 absentable_type character varying NOT NULL,
 absentable_id bigint NOT NULL,
 absence_type integer DEFAULT 0,
 starting_date timestamp(6) without time zone NOT NULL,
 ending_date timestamp(6) without time zone NOT NULL,
 period daterange GENERATED ALWAYS AS (daterange((starting_date)::date, ((ending_date)::date + 1), '[)'::text)) STORED,
 created_at timestamp(6) without time zone NOT NULL,
 updated_at timestamp(6) without time zone NOT NULL
);
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE ONLY public.absences
 ADD CONSTRAINT index_absences_no_overlapping_periods 
 EXCLUDE USING gist ( absentable_id WITH =
 , absentable_type WITH =
 , absence_type WITH =
 , period WITH &&);

INSERT INTO "absences" 
("classroom_id", "absentable_type", "absentable_id", "absence_type", "starting_date", "ending_date", "created_at", "updated_at") 
VALUES 
(1, 'Student', 147, 0, '2025-09-29 06:30:00', '2025-09-29 10:00:00', '2025-10-01 09:06:37.736167', '2025-10-01 09:06:37.736167') 
RETURNING "id", "period"

id	period
1	[2025年09月29日,2025年09月30日)

First I was using daterange((starting_date)::date, (ending_date)::date) but if starging_date and ending_date are on the same day, Postgresql stores ending_date + 1 day.

My current usage of daterange on AS uses
daterange((starting_date)::date, ((ending_date)::date + 1), '[)'::text).

On both cases, range is over 2 days.

Maybe my approach using virtual column to add a constrain on is not correct. Changing from daterange to tsrange side-steps the issue.

• Panagiotis Kanavos: What's the actual table definition and how did you determine there's a problem? Think what you actually asked - an exclusive upper bound that covers the current date. That means the stored upper bound must be the next date. Since it's exclusive though, the range won't match the next date
• Laurenz Albe: Your question needs clarification, please edit it. Are you really talking about columns of type time? If start and end of the range are identical, the range should be empty, but you say it is different. I think that it would be best to show the actual data, the SQL statements you are running and the actual result, then there is no room for misunderstanding.
• Panagiotis Kanavos: I can't reproduce a problem. To store a single day period the upper bound must be inclusive, otherwise empty is returned. When I use create table test (start_date date, end_date date, valid_range daterange generated always as (daterange(start_date,end_date.'[]')) stored , EXCLUDE USING GIST (valid_range WITH &&) ); in db-fiddle the constraint works. The single-day stored range appears as [2025年01月01日,2025年01月02日) which is fine
• Zegarek: In neither case the range is 2 days. The doc mentions that all discrete ranges are stored in canonical form with lower inclusive, upper exclusive bounds, meaning that it makes no difference whether you do daterange('today','tomorrow','[)') or daterange('today','today','[]') - both get saved as the former, and they both mean the same thing: today until the end of today, so, just today. I think you just misinterpreted that one part and a simple test can show both of your constraints do the thing you wanted.
• Zegarek: Good point, the nothing affected by TimeZone setting can work in the constraint/index expression - take it part as a general remark. Still, there should be no need for it here since your initial daterange() idea works just fine.

Answer 1

In both cases, range is over 2 days.

In neither case the range is 2 days. Quoting 8.17.7. Discrete Range Types:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [).

This means that it makes no difference whether you do

• daterange('today','tomorrow','[)') or
• daterange('today','today','[]')

These both get saved as the former, and they both mean the same thing: today until the end of today, so, just today. I think you just misinterpreted that one part and a simple test can show both of your constraints do the thing you wanted: _{demo at db<>fiddle}

INSERT INTO "absences" 
("classroom_id", "absentable_type", "absentable_id", "absence_type", "starting_date", "ending_date", "created_at", "updated_at") 
VALUES 
(1, 'Student', 147, 0, '2025-09-29 06:30:00', '2025-09-29 10:00:00', '2025-10-01 09:06:37.736167', '2025-10-01 09:06:37.736167') 
RETURNING "id", "period"

id	period
1	[2025年09月29日,2025年09月30日)

INSERT INTO "absences" 
("starting_date", "ending_date", "absentable_type", "absentable_id", "absence_type", "classroom_id", "created_at", "updated_at") 
SELECT "starting_date"::date+1, "ending_date"::date+1, "absentable_type", "absentable_id", "absence_type", "classroom_id", "created_at"+'1s'::interval, "updated_at"+'2s'::interval
FROM "absences"
LIMIT 1
RETURNING "id", "period";

id	period
2	[2025年09月30日,2025年10月01日)

The end of the first period is excluded from it, so it doesn't overlap with the (included) beginning of the second one.

---

If you're not really using the period column for anything else, you can remove it and still use the same expression in the constraint:

ALTER TABLE public.absences
 DROP CONSTRAINT index_absences_no_overlapping_periods;
ALTER TABLE public.absences
 DROP COLUMN period;
ALTER TABLE public.absences
 ADD CONSTRAINT index_absences_no_overlapping_periods 
 EXCLUDE USING gist ( absentable_id WITH =
 , absentable_type WITH =
 , absence_type WITH =
 , (daterange((starting_date)::date, ((ending_date)::date + 1), '[)'::text)) WITH &&);

As already pointed out by @Frank Heikens, there's no need to keep both and I agree the range type value is more convenient to use.