Re: Finding free time period on non-continous tstzrange field values

Поиск
Список
Период
Сортировка
Искать
От
Amitabh Kant
Тема
Re: Finding free time period on non-continous tstzrange field values
Дата
Msg-id
CAPTAQB++ZfCivCb7FSsDmRrTYvSd2_wJarXjh5EBdp2HO0ACHA@mail.gmail.com
Ответ на
Finding free time period on non-continous tstzrange field values (Amitabh Kant)
Список
Дерево обсуждения
Finding free time period on non-continous tstzrange field values Amitabh Kant <amitabhkant@gmail.com>
Re: Finding free time period on non-continous tstzrange field values Marcos Pegoraro <marcos@f10.com.br>
Re: Finding free time period on non-continous tstzrange field values Amitabh Kant <amitabhkant@gmail.com>
Re: Finding free time period on non-continous tstzrange field values Amitabh Kant <amitabhkant@gmail.com>
Re: Finding free time period on non-continous tstzrange field values Tom Lane <tgl@sss.pgh.pa.us>
Re: Finding free time period on non-continous tstzrange field values Amitabh Kant <amitabhkant@gmail.com>


On Wed, Nov 30, 2022 at 7:13 PM Amitabh Kant <amitabhkant@gmail.com> wrote:
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


SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022-11-30 00:00:00', '[]')) -
  range_agg(time_range) AS availability
FROM test_time_range
WHERE time_range && tstzrange('2022-11-25 00:00:00', '2022-11-30 00:00:00', '[]');

but then I receive the following error. My guess is I need to cast the tstzrange output, but can't seem to find the correct cast.

ERROR: function tstzmultirange(tstzrange) does not exist
LINE 1: SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 8
В списке pgsql-general по дате отправления
Предыдущее
От: Preston Zimmerer
Дата:
Следующее
От: Вадим Самохин
Дата:
FAQ