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

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>