Thread: Using make_timestamp() to create a BC value
Everything that I say here applies to "make_timestamptz()", too. But my code examples need less typing when I use plain "make_timestamp()".
create procedure assert_make_ts_from_extracted_fields_from_ts_ok(ts in timestamp)
language plpgsql
as $body$
declare
year constant int not null := extract(year from ts);
month constant int not null := extract(month from ts);
mday constant int not null := extract(day from ts);
hour constant int not null := extract(hour from ts);
min constant int not null := extract(min from ts);
sec constant double precision not null := extract(sec from ts);
begin
raise info 'year: %', year;
declare
ts_from_extracted_fields constant timestamp not null :=
make_timestamp(year, month, mday, hour, min, sec);
begin
assert (ts_from_extracted_fields = ts), 'assert failed';
end;
end;
It would seem to me that ordinary principles of good software design let one expect that the rule that this procedure tests ought to hold:
language plpgsql
as $body$
declare
year constant int not null := extract(year from ts);
month constant int not null := extract(month from ts);
mday constant int not null := extract(day from ts);
hour constant int not null := extract(hour from ts);
min constant int not null := extract(min from ts);
sec constant double precision not null := extract(sec from ts);
begin
raise info 'year: %', year;
declare
ts_from_extracted_fields constant timestamp not null :=
make_timestamp(year, month, mday, hour, min, sec);
begin
assert (ts_from_extracted_fields = ts), 'assert failed';
end;
end;
$body$;
Indeed, it does hold—for AD timestamps:
call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 AD');
call assert_make_ts_from_extracted_fields_from_ts_ok(localtimestamp);
call assert_make_ts_from_extracted_fields_from_ts_ok(localtimestamp);
The assertion holds for each invocation. And the expected "year" values, "1" and "2021", are reported.
But this invocation makes the assertion fail:
call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 BC');
The 22008 error is reported thus:
date field value out of range: -1-01-01
(And sure enough, "raise info 'year: %', year" reports "-1".)
The doc for "make_timestamp()" here:
is totally silent about the limitation that a positive year argument must be used.
This means that I need to implement my own baroque version thus:
create function my_make_timestamp(
year int, month int, mday int, hour int, min int, sec double precision)
returns timestamp
language plpgsql
as $body$
declare
bc constant boolean not null := year < 0 ;
t constant timestamp not null := make_timestamp(abs(year), month, mday, hour, min, sec);
begin
return case bc
when true then (t::text||' BC')::timestamp
else t
end;
end;
$body$;
year int, month int, mday int, hour int, min int, sec double precision)
returns timestamp
language plpgsql
as $body$
declare
bc constant boolean not null := year < 0 ;
t constant timestamp not null := make_timestamp(abs(year), month, mday, hour, min, sec);
begin
return case bc
when true then (t::text||' BC')::timestamp
else t
end;
end;
$body$;
If I replace "make_timestamp()" in "assert_make_ts_from_extracted_fields_from_ts_ok()" with "my_make_timestamp()" then (of course) I get (what I regard as) the proper behavior.
Why must I do this? Am I missing something?
On 10/6/21 2:59 PM, Bryn Llewellyn wrote: > Everything that I say here applies to "make_timestamptz()", too. But my > code examples need less typing when I use plain "make_timestamp()". > > (And sure enough, "raise info 'year: %', year" reports "-1".) > > The doc for "make_timestamp()" here: > > www.postgresql.org/docs/14/functions-datetime.html > <http://www.postgresql.org/docs/14/functions-datetime.html> > What version of Postgres are you using? Postgres 13: select extract(year from '0001-01-01 00:00:00 BC'::timestamp); date_part ----------- -1 select make_timestamp(-1,1,1,2,30,0); ERROR: date field value out of range: -1-01-01 Postgres 14: select extract(year from '0001-01-01 00:00:00 BC'::timestamp); date_part ----------- -1 select make_timestamp(-1,1,1,2,30,0); make_timestamp ------------------------ 0001-01-01 02:30:00 BC Postgres release notes https://www.postgresql.org/docs/current/release-14.html#id-1.11.6.5.3 Allow make_timestamp()/make_timestamptz() to accept negative years (Peter Eisentraut) Negative values are interpreted as BC years. -- Adrian Klaver adrian.klaver@aklaver.com
Bryn Llewellyn <bryn@yugabyte.com> writes:
> But this invocation makes the assertion fail:
> call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 BC');
> The 22008 error is reported thus:
> date field value out of range: -1-01-01
As of v14, make_timestamp[tz] will accept negative year values as
meaning BC. Before that, they threw errors.
regards, tom lane
tgl@sss.pgh.pa.us wrote:Bryn wrote:But this invocation makes the assertion fail:call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 BC');
The 22008 error is reported thus:
date field value out of range: -1-01-01
As of v14, make_timestamp[tz] will accept negative year values as meaning BC. Before that, they threw errors.
Ah… that's excellent news. Thanks for the lightening fast response, Tom. Thanks, too, to Adrian who passed on similar news, and this link:
I did my test using PostgreSQL 13.4. Forgive me for not mentioning this.