Using make_timestamp() to create a BC value

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Using make_timestamp() to create a BC value
Date: 2021-10-06 21:59:28
Message-ID: 010C91D3-9982-445D-B3C1-B20BD1E8C841@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

www.postgresql.org/docs/14/functions-datetime.html

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2021-10-06 22:26:17 Re: Using make_timestamp() to create a BC value
Previous Message Troy Hardin 2021-10-06 16:43:48 Re: [Ext:] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start