Finding free time period on non-continous tstzrange field values

From: Amitabh Kant <amitabhkant(at)gmail(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Finding free time period on non-continous tstzrange field values
Date: 2022-11-30 13:43:45
Message-ID: CAPTAQBJn_BRL4hLYcCpVmn_FQ2gCpiQXv5FFbkGwV2E5hrdVJA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

Given the following table, how do I find free time period.

CREATE TABLE test_time_range (
id SERIAL PRIMARY KEY,
time_range tstzrange);

Insert into test_time_range(time_range) values('[2022-11-28 08:00:00,
2022-11-28 20:00:00]');
Insert into test_time_range(time_range) values('[2022-11-29 12:30:00,
2022-11-29 22:00:00]');
Insert into test_time_range(time_range) values('[2022-11-30 05:00:00,
2022-11-30 19:00:00]');

In the above example, I would like the query to return something like this:
"2022-11-28 20:01:00 2022-11-29 11:29:00"
"2022-11-29 22:01:00 2022-11-30 04:59:00"

Apologies if this is a dumb question, but trying to use range for the first
time , and can't get my head around it.

Using PG14, can upgrade to 15 if that matters.

Amitabh

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marcos Pegoraro 2022-11-30 13:50:14 Re: Finding free time period on non-continous tstzrange field values
Previous Message Alvaro Herrera 2022-11-30 08:37:09 Re: PostgreSQL extension for processing Graph queries (Apache AGE)