Обсуждение: to_timestamp function

Поиск
Список
Период
Сортировка

to_timestamp function

От
Gustavsson Mikael
Дата:
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

Re: to_timestamp function

От
Tom Lane
Дата:
Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:
> So my question is, is it intentional that to_timestamp is stricter than cast to timestamp?

Yes.  The point of using that function at all is to be strict about the
input format, so being strict about the field values seems to make
sense along with that.  An independent argument for it is mentioned in
the commit message (d3cd36a13):

    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.

            regards, tom lane

SV: to_timestamp function

От
Gustavsson Mikael
Дата:
Thanks for fast reply!

I'll forward the answer to my developers.

kr
Mikael Gustavsson
________________________________________
Från: Tom Lane [tgl@sss.pgh.pa.us]
Skickat: den 20 mars 2019 17:33
Till: Gustavsson Mikael
Kopia: pgsql-general@lists.postgresql.org
Ämne: Re: to_timestamp function

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:
> So my question is, is it intentional that to_timestamp is stricter than cast to timestamp?

Yes.  The point of using that function at all is to be strict about the
input format, so being strict about the field values seems to make
sense along with that.  An independent argument for it is mentioned in
the commit message (d3cd36a13):

    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.

                        regards, tom lane