Finding free time period on non-continous tstzrange field values
| От | Amitabh Kant |
|---|---|
| Тема | Finding free time period on non-continous tstzrange field values |
| Дата | |
| Msg-id | CAPTAQBJn_BRL4hLYcCpVmn_FQ2gCpiQXv5FFbkGwV2E5hrdVJA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Finding free time period on non-continous tstzrange field values
Re: Finding free time period on non-continous tstzrange field values |
| Список | 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]');
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"
"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
В списке pgsql-general по дате отправления: