Search Postgresql Archives

Re: to_date() and to_timestamp() with negative years

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> tgl@xxxxxxxxxxxxx wrote:
> 
> Bryn Llewellyn <bryn@xxxxxxxxxxxx> writes:
>> Is there any chance that you might be bold and simply make negative "year" values illegal in "to_date()" and "to_timestamp()" — just as they already are in "make_timestamp()", "make_timestamptz()", and the "from text" typecasts to date-time moment values?
> 
> Uh, what?
> 
> regression=# select make_timestamp(-44,3,15,0,0,0);
>     make_timestamp     
> ------------------------
> 0044-03-15 00:00:00 BC
> (1 row)
> 
> The other stuff you are talking about looks like confusion around which
> characters are minus signs in the data and which ones are field
> separators.  Given the very squishy definitions of to_date/to_timestamp,
> I'm not surprised if that works only with carefully chosen field
> layouts --- but it does work for me with all of these cases:
> 
> regression=# select to_date('-0044-03-15', 'YYYY-MM-DD');
>    to_date    
> ---------------
> 0044-03-15 BC
> (1 row)
> 
> regression=# select to_date('03-15--0044', 'MM-DD-YYYY');
>    to_date    
> ---------------
> 0044-03-15 BC
> (1 row)
> 
> regression=# select to_date('03/15/-0044', 'MM/DD/YYYY');
>    to_date    
> ---------------
> 0044-03-15 BC
> (1 row)
> 
> I'd be the first to agree that that code is a mess and could stand to
> be rewritten --- but I seriously doubt that we'd take a patch that
> intentionally breaks cases that work fine today.  There's also the
> angle that these are supposed to be Oracle-compatible, so I wonder
> what Oracle does with such input.

I just ran your test:

select make_timestamp(-44,3,15,0,0,0);

in each of the three environments that I mentioned—but especially, therefore, in PG 13.4 (on macOS).

It cased the error that I mentioned:

ERROR:  22008: date field value out of range: -44-03-15

It's the same with "make_timestamp()".

In what PG version did you run your test?

I'm not surprised that grandfathered-in "solution" that I described will never go away—despite its questionable conceptual basis.








[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux