to_timestamp function

[Gustavsson Mikael <mikael.gustavsson@xxxxxxx>]

Hi,

We recently upgraded from PG9.6 to PG11 and I have some questions regarding the function to_timestamp.

We have an application that for some reason unknown to me uses timestamps with hour = 24. 

After upgrade we got som problems with this. The solution is to use cast instead of the function but im curious if this is intentional.

Examles:

In PG9.6 this works:

to_timestamp('2019-03-20 24:00','YYYY-MM-DD HH24:MI')

but in PG11 we get:

ERROR:  date/time field value out of range.

I assume this have something to do with this row in releasenotes for PG10:

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.

But.

This works in both PG9.6 and PG11:

'2019-03-20 24:00'::timestamp with time zone

and this generates an error in both PG9.6 and PG11:




'2009-06-40'::timestamp with time zone


So my question is, is it intentional that to_timestamp is stricter than cast to timestamp?

kr

Mikael Gustavsson

SMHI / Swedish Meteorological and Hydrological Institute