From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sébastien Caunes <sebastien@pixseed.fr>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Bug report function to_date should reject HH24:MI:SS formatting options
Date: Thu, 30 Sep 2021 09:35:18 -0400
Message-ID: <3058340.1633008918@sss.pgh.pa.us> (raw)
In-Reply-To: <CANtq+vRSFrA6R7X1KXkiMVL7=uQFfEWjkKC=o8r358ySiNTMig@mail.gmail.com>
References: <CANtq+vRSFrA6R7X1KXkiMVL7=uQFfEWjkKC=o8r358ySiNTMig@mail.gmail.com>
=?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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, sebastien@pixseed.fr, pgsql-bugs@lists.postgresql.org
Subject: Re: Bug report function to_date should reject HH24:MI:SS formatting options
In-Reply-To: <3058340.1633008918@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox