Thread: EXCLUDE USING and tstzrange
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? Thanks ! Laura
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'supper 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 !