Everything that I say here applies to "make_timestamptz()", too. But my code examples need less typing when I use plain "make_timestamp()".
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:
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;
$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);
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$;
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?