From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?S=C3=A9bastien_Caunes?= <sebastien@pixseed.fr>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Bug report function to_date should reject HH24:MI:SS formatting
 options
In-reply-to: 
 <CANtq+vRSFrA6R7X1KXkiMVL7=uQFfEWjkKC=o8r358ySiNTMig@mail.gmail.com>
References: 
 <CANtq+vRSFrA6R7X1KXkiMVL7=uQFfEWjkKC=o8r358ySiNTMig@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?S=C3=A9bastien_Caunes?= <sebastien@pixseed.fr>
	message dated "Thu, 30 Sep 2021 11:03:14 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3058339.1633008918.1@sss.pgh.pa.us>
Date: Thu, 30 Sep 2021 09:35:18 -0400
Message-ID: <3058340.1633008918@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3058340.1633008918%40sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?S=C3=A9bastien_Caunes?= <sebastien@pixseed.fr> writes:
> TO_DATE function should reject the time part (HH24:MI:SS) in the template
> string.

We're not going to do that, because it would break interesting use-cases.
It's true that you could just omit the time part of the format in the
example you gave.  But if you want to parse data with the time part
appearing first, that doesn't work:

regression=# select TO_DATE('08:30:37 2021-09-30', 'HH24:MI:SS YYYY-MM-DD');
  to_date   
------------
 2021-09-30
(1 row)

regression=# select TO_DATE('08:30:37 2021-09-30', 'YYYY-MM-DD');
ERROR:  date/time field value out of range: "08:30:37 2021-09-30"

> I just saw developers coming from Oracle spending a lot of time on this.

A rule of thumb for people coming from Oracle is that uses of to_date
and related functions should be nuked from orbit.  For just about any
common datetime layout, simply casting to the desired type is safer
and easier:

regression=# select '2021-09-30 08:30:37'::timestamp;
      timestamp      
---------------------
 2021-09-30 08:30:37
(1 row)

You only need to_date and friends to parse weird formats (like
time-of-day first).  Our docs do make this point, but perhaps
not forcefully enough.

			regards, tom lane