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.kla...@aklaver.com> wrote: > 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.kla...@aklaver.com >