Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0eFj-0005FS-Rg for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 07:45:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0eFi-0005UQ-Qq for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 07:45:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0eFi-0005R2-9D for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 07:45:02 +0000 Received: from mail-wr1-x436.google.com ([2a00:1450:4864:20::436]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p0eFf-0000Zc-Bx for pgsql-general@postgresql.org; Thu, 01 Dec 2022 07:45:01 +0000 Received: by mail-wr1-x436.google.com with SMTP id z4so1323231wrr.3 for ; Wed, 30 Nov 2022 23:44:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=pCqyq+1eb1qiam62aoP2JajArpD4L92xs0Ko8gjsGt8=; b=bCRGwRV6hq1fh5mcTkOp4dYmkOvBgXZ02WWS+ZlWEwdcVgI3k3mH5Itv6nK0NvpynS GxEa1XnQTzNKCNHB6IWs09GOTz643V5jvCC4fKx2zMWNeF3kh8liX3lFcdCWw6mY2qNO pYfp79dz4yfrRCAxQK4QQON3TlwW6L3nfIC07LxJAaNWASQijmC/BXhkJ6VfmNKtD6f3 hB6XNWYjLCST2HdHkmMt+OwaeqLn8G/inwCaxG+sZA2xEk4ScksfljWfFGKYDvCWIT0O 8tY7XI4Vp//zQ32vi/hO2CbtNrafskgSpVpiPnKpYDU1ST1JHPt9BaazFrvdpt89n9u8 l1eQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=pCqyq+1eb1qiam62aoP2JajArpD4L92xs0Ko8gjsGt8=; b=yX5tytydx++a5c42EifolxEzusZrQb7AmMzgwo0ieXfa5QYo8dJUlUhMnC/3H6Zlmq 1zZ8qZ383g8QTt6Rw7zY/Iw5wsQ8276OM29IUkqJLeINl8feS3fZOq0lB8w8fLdEJs9t FI97P8Ojz6OAS6pYRgAyYx3YrI836IF344JBStpSpOdcb/U7ZjiebdO+yUzazc/pZRJf wD+Tcyom3HoRgVpIGtaXY/wkuQuP8mzYOioS0fXnkRtV+jp3drjlgX6n+ZM4vzZSEhty nLobrVr+NRykSgAySZhl7cE6jkvRpdtDHhip0bzVJuuzdqBxG2EhLyW5RnXveoXVZ4CV gY3Q== X-Gm-Message-State: ANoB5plozPEOUSUadfn2RThdAa9sqemlvU5oR5WNe6m/1wsCBm9iOB4x IV86aMpNcaJ+yf1ZClRCTCT2GMuscOUHEGtCzKnvR4JGTi0= X-Google-Smtp-Source: AA0mqf7S4RQl5NTNvCK3nMLqJIaOV8h03/ZiEzGKUJLimXqO313mTINUJfxSSIsmKyq3R3kpBZZsabokKBdUF9yF1YA= X-Received: by 2002:a5d:6144:0:b0:242:1c33:35b8 with SMTP id y4-20020a5d6144000000b002421c3335b8mr10446953wrt.217.1669880697300; Wed, 30 Nov 2022 23:44:57 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Amitabh Kant Date: Thu, 1 Dec 2022 13:14:20 +0530 Message-ID: Subject: Re: Finding free time period on non-continous tstzrange field values To: PGSQL Mailing List Cc: Marcos Pegoraro Content-Type: multipart/alternative; boundary="000000000000c7ac2105eebf65ee" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c7ac2105eebf65ee Content-Type: text/plain; charset="UTF-8" On Wed, Nov 30, 2022 at 7:13 PM Amitabh Kant 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 --000000000000c7ac2105eebf65ee Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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.

CREA= TE TABLE test_time_range (
=C2=A0 =C2=A0 id SERIAL PRIMARY KEY,
=C2= =A0 =C2=A0 time_range tstzrange);


Insert into test_time_range(ti= me_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, 2= 022-11-29 22:00:00]');
Insert into test_time_range(time_range) value= s('[2022-11-30 05:00:00, 2022-11-30 19:00:00]');



In the above example, I would like th= e 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 ques= tion, but trying to use range for the first time , and can't get my hea= d around it.=C2=A0

Using PG14, can upgrade to 15 i= f that matters.


Amitabh
=

SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00'= ;, '2022-11-30 00:00:00', '[]')) -
=C2=A0 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', '[]');

b= ut then I receive the following error. My guess is I need to cast the tstzr= ange output, but can't seem to find the correct cast.

ERROR: function tstzmultirange(tstzrange) does not exist<= /font>
LINE 1: SELECT tstzmultirang= e(tstzrange('2022-11-25 00:00:00', '2022... ^ HINT: No function matches the given name and argument types. You might nee= d to add explicit type casts. SQL state: 42883 Character: 8
--000000000000c7ac2105eebf65ee--