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