pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Amitabh Kant <amitabhkant@gmail.com>
To: PGSQL Mailing List <pgsql-general@postgresql.org>
Cc: Marcos Pegoraro <marcos@f10.com.br>
Subject: Re: Finding free time period on non-continous tstzrange field values
Date: Thu, 1 Dec 2022 13:14:20 +0530
Message-ID: <CAPTAQB++ZfCivCb7FSsDmRrTYvSd2_wJarXjh5EBdp2HO0ACHA@mail.gmail.com> (raw)
In-Reply-To: <CAPTAQBJn_BRL4hLYcCpVmn_FQ2gCpiQXv5FFbkGwV2E5hrdVJA@mail.gmail.com>
References: <CAPTAQBJn_BRL4hLYcCpVmn_FQ2gCpiQXv5FFbkGwV2E5hrdVJA@mail.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
>

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

view thread (6+ messages)  latest in thread

Message-ID: <CAPTAQB++ZfCivCb7FSsDmRrTYvSd2_wJarXjh5EBdp2HO0ACHA@mail.gmail.com>
Permalink:  ../CAPTAQB++ZfCivCb7FSsDmRrTYvSd2_wJarXjh5EBdp2HO0ACHA@mail.gmail.com/
Also on:    postgresql.org/message-id/CAPTAQB++ZfCivCb7FSsDmRrTYvSd2_wJarXjh5EBdp2HO0ACHA@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: amitabhkant@gmail.com, marcos@f10.com.br
  Subject: Re: Finding free time period on non-continous tstzrange field values
  In-Reply-To: <CAPTAQB++ZfCivCb7FSsDmRrTYvSd2_wJarXjh5EBdp2HO0ACHA@mail.gmail.com>
  References: <CAPTAQBJn_BRL4hLYcCpVmn_FQ2gCpiQXv5FFbkGwV2E5hrdVJA@mail.gmail.com> <CAPTAQB++ZfCivCb7FSsDmRrTYvSd2_wJarXjh5EBdp2HO0ACHA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox