Thread: Finding free time period on non-continous tstzrange field values
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]');
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"
"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
Given the following table, how do I find free time period.
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.
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:
HiGiven 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 :
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: 8Amitabh 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