EXCLUDE USING and tstzrange

On 6/4/21 7:32 AM, Laura Smith wrote:

Hi,

I'm having difficulty finding the right part of the docs for this one.

Could someone kindly clarify:

create table test (
test_id text,
test_range tstzrange);

Will "EXCLUDE USING gist (test_id WITH =, test_range WITH && )" work as expected or do I need to use "EXCLUDE USING gist (test_id WITH =, test_range WITH TIME ZONE && )" to ensure the timezone is correctly taken into account during comparison ?

tstzrange is over timestamp with time zone, so time zones are already
taken into account.

Thanks !

Laura

--
Adrian Klaver
adrian.klaver@aklaver.com

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 15:44, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 6/4/21 7:32 AM, Laura Smith wrote:

Hi,
I'm having difficulty finding the right part of the docs for this one.
Could someone kindly clarify:
create table test (
test_id text,
test_range tstzrange);
Will "EXCLUDE USING gist (test_id WITH =, test_range WITH && )" work as expected or do I need to use "EXCLUDE USING gist (test_id WITH =, test_range WITH TIME ZONE && )" to ensure the timezone is correctly taken into account during comparison ?

tstzrange is over timestamp with time zone, so time zones are already
taken into account.

Thanks !
Laura

--

Adrian Klaver
adrian.klaver@aklaver.com

Thank you Adrian !

One other question, what's the syntax for manipulating only the upper bound of a range.

Say I have a Postgres function that does a "SELECT INTO" for an existing tsrange. Is there an easy way to change the variable's upper bound whilst leaving the "old" lower bound intact ?

On 6/4/21 10:58 AM, Laura Smith wrote:

One other question, what's the syntax for manipulating only the upper
bound of a range.

Say I have a Postgres function that does a "SELECT INTO" for an
existing tsrange. Is there an easy way to change the variable's
upper bound whilst leaving the "old" lower bound intact ?

There may be easier/better ways, but for example this works:

8<------------------------------
insert into test
values(42, '[2021-01-01, 2021-06-03)');
INSERT 0 1

select test_range from test where test_id = '42';
test_range
-----------------------------------------------------
["2021-01-01 00:00:00-05","2021-06-03 00:00:00-04")
(1 row)

update test
set test_range = tstzrange(lower(test_range),
'2021-06-04', '[)')
where test_id = '42';
UPDATE 1

select test_range from test where test_id = '42';
test_range
-----------------------------------------------------
["2021-01-01 00:00:00-05","2021-06-04 00:00:00-04")
(1 row)
8<------------------------------

HTH,

Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 16:20, Joe Conway <mail@joeconway.com> wrote:

On 6/4/21 10:58 AM, Laura Smith wrote:

One other question, what's the syntax for manipulating only the upper
bound of a range.
Say I have a Postgres function that does a "SELECT INTO" for an
existing tsrange. Is there an easy way to change the variable's
upper bound whilst leaving the "old" lower bound intact ?

There may be easier/better ways, but for example this works:

8<------------------------------
insert into test
values(42, '[2021-01-01, 2021-06-03)');
INSERT 0 1

select test_range from test where test_id = '42';
test_range

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

["2021-01-01 00:00:00-05","2021-06-03 00:00:00-04")
(1 row)

update test
set test_range = tstzrange(lower(test_range),
'2021-06-04', '[)')
where test_id = '42';
UPDATE 1

select test_range from test where test_id = '42';
test_range

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

["2021-01-01 00:00:00-05","2021-06-04 00:00:00-04")
(1 row)
8<------------------------------

HTH,

Joe

--------------------------------------------------------------------------------------------------------

Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Thanks Joe !