EXCLUDE USING and tstzrange

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

EXCLUDE USING and tstzrange

Laura Smith
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


Reply | Threaded
Open this post in threaded view
|

Re: EXCLUDE USING and tstzrange

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: EXCLUDE USING and tstzrange

Laura Smith



Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 15:44, Adrian Klaver <[hidden email]> 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
> [hidden email]


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 ?



Reply | Threaded
Open this post in threaded view
|

Re: EXCLUDE USING and tstzrange

Joe Conway
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


Reply | Threaded
Open this post in threaded view
|

Re: EXCLUDE USING and tstzrange

Laura Smith



Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 16:20, Joe Conway <[hidden email]> 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 !