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?
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 |