Finding free time period on non-continous tstzrange field values

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

https://www.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3

On Wed, Nov 30, 2022 at 7:20 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

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

https://www.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3

Thanks Marcos .. Had seen this earlier but somehow slipped my mind to use
it for implementation. Will try implementing using tstzmultirange.

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

Based on Marcos suggestions (
https://www.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3
), I tried the following query :

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

Amitabh Kant <amitabhkant@gmail.com> writes:

I tried the following query :

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

That function certainly should exist:

psql (14.6)
Type "help" for help.

postgres=# \df tstzmultirange
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------+------------------+----------------------+------
pg_catalog | tstzmultirange | tstzmultirange | | func
pg_catalog | tstzmultirange | tstzmultirange | VARIADIC tstzrange[] | func
pg_catalog | tstzmultirange | tstzmultirange | tstzrange | func
(3 rows)

My guess is that your server is not in fact PG14, but some
older version.

regards, tom lane

On Thu, Dec 1, 2022 at 7:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amitabh Kant <amitabhkant@gmail.com> writes:

I tried the following query :

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

That function certainly should exist:

psql (14.6)
Type "help" for help.

postgres=# \df tstzmultirange
List of functions
Schema | Name | Result data type | Argument data types |
Type

------------+----------------+------------------+----------------------+------
pg_catalog | tstzmultirange | tstzmultirange | |
func
pg_catalog | tstzmultirange | tstzmultirange | VARIADIC tstzrange[] |
func
pg_catalog | tstzmultirange | tstzmultirange | tstzrange |
func
(3 rows)

My guess is that your server is not in fact PG14, but some
older version.

regards, tom lane

My apologies as indeed was the case. Server is running 13.6 .... Will
update the server and try it again.

Amitabh