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.klaver@aklaver.com