Re: Converter VARCHAR para TIMESTAMP com milleseconds ERROR: date/time field value out of range

Em ter, 9 de out de 2018 às 09:24, Cleiton Luiz Domazak
<cleitondomazak(at)gmail(dot)com> escreveu:
> Exato, minha dúvida é, por que a partir da versão 10, esse comportamente mudou, e ocorre um erro ao converter o mesmo valor.
>
Mudou porque o to_timestamp e to_date não checavam o intervalo de cada
um de seus campos e, por serem clones das funções do Oráculo, o
comportamento deveria ser checar o erro. Acho que isso passou
despercebido e não incluiram na seção incompatiblidades mas a mudança
está descrita nas notas de lançamento [1] como abaixo:

Make to_timestamp() and to_date() reject out-of-range input fields
(Artur Zakirov)
For example, previously to_date('2009-06-40','YYYY-MM-DD') was
accepted and returned 2009-07-10. It will now generate an error.

As mudanças no código foram aplicadas pelo commit abaixo:

commit d3cd36a133d96ad5578b6c10279b55fd5b538093
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Wed Sep 28 14:36:04 2016 -0400

Make to_timestamp() and to_date() range-check fields of their input.

Historically, something like to_date('2009-06-40','YYYY-MM-DD') would
return '2009-07-10' because there was no prohibition on out-of-range
month or day numbers. This has been widely panned, and it also turns
out that Oracle throws an error in such cases. Since these functions
are nominally Oracle-compatibility features, let's change that.

There's no particular restriction on year (modulo the fact that the
scanner may not believe that more than 4 digits are year digits,
a matter to be addressed separately if at all). But we now check month,
day, hour, minute, second, and fractional-second fields, as well as
day-of-year and second-of-day fields if those are used.

Currently, no checks are made on ISO-8601-style week numbers or day
numbers; it's not very clear what the appropriate rules would be there,
and they're probably so little used that it's not worth sweating over.

Artur Zakirov, reviewed by Amul Sul, further adjustments by me

Discussion: <1873520224(dot)1784572(dot)1465833145330(dot)JavaMail(dot)yahoo(at)mail(dot)yahoo(dot)com>
See-Also: <57786490(dot)9010201(at)wars-nicht(dot)de>

[1] https://www.postgresql.org/docs/10/static/release-10.html

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento