Re: Using make_timestamp() to create a BC value

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(dot)klaver(at)aklaver(dot)com