Thread: timestamp out of range while casting return value to function'sreturn type
timestamp out of range while casting return value to function'sreturn type
From
Thiemo Kellner
Date:
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
Re: timestamp out of range while casting return value to function'sreturn type
From
Thiemo Kellner
Date:
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
Re: timestamp out of range while casting return value to function'sreturn type
From
Karsten Hilbert
Date:
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
Re: timestamp out of range while casting return value to function'sreturn type
From
Thiemo Kellner
Date:
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