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@xxxxxxxxxxx
