Обсуждение: timestamp out of range while casting return value to function'sreturn type

Hi all

I created a function that ought to return a timestamptz (another  
likewise timestamp) but calling it, I get mentionied error. What do I  
miss? I tried to return a timestamp of the year 2000 to no avail.

Call: select utils.get_max_timestamptz();
--

Function
--
create or replace function GET_MAX_TIMESTAMPTZ()
   returns timestamptz
   language plpgsql
   immutable
   -- Include the hosting schema into search_path so that dblink
   -- can find the pglogger objects. There is no need to access
   -- objects in other schematas not covered with public.
   as
$body$
     begin
         -- highest timestamps on 64bit lubuntu vanilla PostgreSQL 11.3
         return '294277-01-01 00:59:59.999999'::timestamptz;
     end;
$body$;

Kind regards

Thiemo

-- 
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B

Thiemo Kellner <thiemo@gelassene-pferde.biz> writes:
> I created a function that ought to return a timestamptz (another  
> likewise timestamp) but calling it, I get mentionied error. What do I  
> miss?

Whether that:

>          return '294277-01-01 00:59:59.999999'::timestamptz;

is in range or not depends on your timezone setting.  It would
be considered in-range in UTC+1 or further east (so that the date
wraps back to 294276AD); in or west of Greenwich, not so much.

> I tried to return a timestamp of the year 2000 to no avail.

Hardly ...

            regards, tom lane

Hi Tom

Thanks for replying so fast. You are absolutely right.

I changed the code file but failed to install it. :-( I am sorry for  
not checking the obvious.

Kind regards

Thiemo

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

> Thiemo Kellner <thiemo@gelassene-pferde.biz> writes:
>> I created a function that ought to return a timestamptz (another
>> likewise timestamp) but calling it, I get mentionied error. What do I
>> miss?
>
> Whether that:
>
>>          return '294277-01-01 00:59:59.999999'::timestamptz;
>
> is in range or not depends on your timezone setting.  It would
> be considered in-range in UTC+1 or further east (so that the date
> wraps back to 294276AD); in or west of Greenwich, not so much.
>
>> I tried to return a timestamp of the year 2000 to no avail.
>
> Hardly ...
>
>             regards, tom lane



-- 
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B

On Sat, Aug 24, 2019 at 12:57:07AM +0000, Thiemo Kellner wrote:

> Call: select utils.get_max_timestamptz();
> --
>
> Function
> --
> create or replace function GET_MAX_TIMESTAMPTZ()
>   returns timestamptz
>   language plpgsql
>   immutable
>   -- Include the hosting schema into search_path so that dblink
>   -- can find the pglogger objects. There is no need to access
>   -- objects in other schematas not covered with public.
>   as
> $body$
>     begin
>         -- highest timestamps on 64bit lubuntu vanilla PostgreSQL 11.3
>         return '294277-01-01 00:59:59.999999'::timestamptz;
>     end;
> $body$;

Also, but that's a nitpick perhaps not relevant to your use case:

This

    $> psql -d gnumed_v22 -U <redacted>
    psql (11.5 (Debian 11.5-1+deb10u1))

    gnumed_v22=> select 'infinity'::timestamptz;
    -[ RECORD 1 ]---------
    timestamptz | infinity

    gnumed_v22=>

is the highest timestamp.

(You *can* count the horses in *your* corral but there's
 always more of them elsewhere ;-)

Just so you are aware.

Best,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Hi Karsten

Thanks for the infinitly good hint. I remembered the infinity  
blurredly somewhen this morning, looked it up in the docs and already  
dumped my functions in favour of the infinity solution. :-) Great,  
that PostgreSQL has the infinity concept! Thanks

Kind regards

Thiemo

Quoting Karsten Hilbert <Karsten.Hilbert@gmx.net>:

> On Sat, Aug 24, 2019 at 12:57:07AM +0000, Thiemo Kellner wrote:
>
>> Call: select utils.get_max_timestamptz();
>> --
>>
>> Function
>> --
>> create or replace function GET_MAX_TIMESTAMPTZ()
>>   returns timestamptz
>>   language plpgsql
>>   immutable
>>   -- Include the hosting schema into search_path so that dblink
>>   -- can find the pglogger objects. There is no need to access
>>   -- objects in other schematas not covered with public.
>>   as
>> $body$
>>     begin
>>         -- highest timestamps on 64bit lubuntu vanilla PostgreSQL 11.3
>>         return '294277-01-01 00:59:59.999999'::timestamptz;
>>     end;
>> $body$;
>
> Also, but that's a nitpick perhaps not relevant to your use case:
>
> This
>
>     $> psql -d gnumed_v22 -U <redacted>
>     psql (11.5 (Debian 11.5-1+deb10u1))
>
>     gnumed_v22=> select 'infinity'::timestamptz;
>     -[ RECORD 1 ]---------
>     timestamptz | infinity
>
>     gnumed_v22=>
>
> is the highest timestamp.
>
> (You *can* count the horses in *your* corral but there's
>  always more of them elsewhere ;-)
>
> Just so you are aware.
>
> Best,
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B



-- 
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B