Assigning values to a range in Pgsql and inclusive / exclusive bounds

On 6/12/20 11:45 AM, Ron Clarke wrote:

Hi,

I've got a simple problem, but I'm convinced that there must be an
elegant solution. I'm a refugee from the world of MSSQL, so I'm still
finding some aspects of PostgreSQL alien.

I'm trying to use the /tstzrange /datatype. My issue is correctly
setting the bound types when assigning values to a range in code (PGSQL).

So if i declare this : e.g.

/declare tx tstzrange := '[today, tomorrow)' ;/

I get the variable tx as expected with the Inclusive '[' lower bound and
exclusive upper ')' bound.

But if I attempt to reassign the value in code within pgsql I can do
this simply, only with '(' syntax for the lower bound i.e. with an
exclusive lower bound, e.g so this works:-

/tx= (Timestamptz 'today', timestamptz 'now' + interval '1 hour');/

but if I try
/tx= [Timestamptz 'today', timestamptz 'now' + interval '1 hour'); /
/
/
this will have syntax errors - as the hidden 'select [' upsets the
parser.   I've tried to include a '[)' in variations of the expression,
but just get various syntax errors..

I've tried many combinations and I can get it to work using casts and
concatenations, e.g. :-

/ tx = CONCAT('[', 'today'::Timestamptz,',',('now' ::Timestamptz +
interval '1 hour'):: timestamptz , ')'):: tstzrange ;/

works but I can't help thinking that I'm missing something much simpler
and more elegant.
How should this actually be done?

Thanks in advance for your advice.

If:

select tstzrange('today', 'tomorrow', '[)');
tstzrange
-------------------------------------------------------
["06/12/2020 00:00:00 PDT","06/13/2020 00:00:00 PDT")

then:

tx tstzrange := tstzrange('today', 'tomorrow', '[)') ;

Not tested.

Ron
Stay safe everyone.

here's an example script to show what I mean:-

/do
//$$
//DECLARE
/

/tx tstzrange := '[today, tomorrow)' ;/

/answer text;/

/BEGIN
/

/RAISE NOTICE 'Start %', tx;/

/answer = tx @> 'today'::Timestamptz;/

/RAISE NOTICE 'today  %', answer;/

/answer = tx @> 'tomorrow'::Timestamptz;/

/RAISE NOTICE 'tomorrow  %', answer;/

/-- ( works
-- tx= (Timestamptz 'today', timestamptz 'now' + interval '1 hour');
/-- [ doesn't work
-- tx= [Timestamptz 'today', timestamptz 'now' + interval '1 hour');
-- working around the parser??
/tx = CONCAT('[', 'today'::Timestamptz,',',('now' ::Timestamptz
+ interval '1 hour'):: /timestamptz , ')'):: tstzrange ;

/RAISE NOTICE 'reassign  %', tx;/

/answer = tx @> 'today'::Timestamptz;/

/RAISE NOTICE 'today  %', answer;/

/answer = tx @> 'now'::Timestamptz;/

/RAISE NOTICE 'now    %', answer;/

/END;/
/$$ /

--
Adrian Klaver
adrian.klaver@aklaver.com

On 6/12/20 11:45 AM, Ron Clarke wrote:

Hi,

I've got a simple problem, but I'm convinced that there must be an
elegant solution. I'm a refugee from the world of MSSQL, so I'm still
finding some aspects of PostgreSQL alien.

I'm trying to use the /tstzrange /datatype. My issue is correctly
setting the bound types when assigning values to a range in code (PGSQL).

So if i declare this : e.g.

/declare tx tstzrange := '[today, tomorrow)' ;/

I get the variable tx as expected with the Inclusive '[' lower bound and
exclusive upper ')' bound.

But if I attempt to reassign the value in code within pgsql I can do
this simply, only with '(' syntax for the lower bound i.e. with an
exclusive lower bound, e.g so this works:-

/tx= (Timestamptz 'today', timestamptz 'now' + interval '1 hour');/

but if I try
/tx= [Timestamptz 'today', timestamptz 'now' + interval '1 hour'); /
/
/
this will have syntax errors - as the hidden 'select [' upsets the
parser.   I've tried to include a '[)' in variations of the expression,
but just get various syntax errors..

I've tried many combinations and I can get it to work using casts and
concatenations, e.g. :-

/ tx = CONCAT('[', 'today'::Timestamptz,',',('now' ::Timestamptz +
interval '1 hour'):: timestamptz , ')'):: tstzrange ;/

works but I can't help thinking that I'm missing something much simpler
and more elegant.
How should this actually be done?

Realized what you want is:

select tstzrange('today', ('now'::timestamptz + interval '1 hour'), '[)');
tstzrange
--------------------------------------------------------------
["06/12/2020 00:00:00 PDT","06/12/2020 13:59:27.554229 PDT")

tx tstzrange := tstzrange('today', ('now'::timestamptz + interval '1
hour'), '[)') ;

Thanks in advance for your advice.

Ron
Stay safe everyone.

here's an example script to show what I mean:-

/do
//$$
//DECLARE
/

/tx tstzrange := '[today, tomorrow)' ;/

/answer text;/

/BEGIN
/

/RAISE NOTICE 'Start %', tx;/

/answer = tx @> 'today'::Timestamptz;/

/RAISE NOTICE 'today  %', answer;/

/answer = tx @> 'tomorrow'::Timestamptz;/

/RAISE NOTICE 'tomorrow  %', answer;/

/-- ( works
-- tx= (Timestamptz 'today', timestamptz 'now' + interval '1 hour');
/-- [ doesn't work
-- tx= [Timestamptz 'today', timestamptz 'now' + interval '1 hour');
-- working around the parser??
/tx = CONCAT('[', 'today'::Timestamptz,',',('now' ::Timestamptz
+ interval '1 hour'):: /timestamptz , ')'):: tstzrange ;

/RAISE NOTICE 'reassign  %', tx;/

/answer = tx @> 'today'::Timestamptz;/

/RAISE NOTICE 'today  %', answer;/

/answer = tx @> 'now'::Timestamptz;/

/RAISE NOTICE 'now    %', answer;/

/END;/
/$$ /

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks for that perfect... missed the use of tstzrange() as a 'function' in
the documentation.

Best regards
Ron

On Fri, 12 Jun 2020 at 21:02, Adrian Klaver <adrian.klaver@aklaver.com>
wrote: