Thread: Bug report function to_date should reject HH24:MI:SS formatting options
Hello,
It is very misleading that you can write this :
SELECT * FROM orders WHERE date BETWEEN TO_DATE('2021-09-29 08:30:00', 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2021-09-30 08:30:37', 'YYYY-MM-DD HH24:MI:SS');
This will ignore the time part and select rows that seem out of range.
TO_DATE function should reject the time part (HH24:MI:SS) in the template string.
SELECT * FROM orders WHERE date BETWEEN TO_DATE('2021-09-29 08:30:00', 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2021-09-30 08:30:37', 'YYYY-MM-DD HH24:MI:SS');
This will ignore the time part and select rows that seem out of range.
TO_DATE function should reject the time part (HH24:MI:SS) in the template string.
I just saw developers coming from Oracle spending a lot of time on this.
Thank you so much for working on this software.
B. R.
Sébastien Caunes
=?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
Re: Bug report function to_date should reject HH24:MI:SS formatting options
From
Sébastien Caunes
Date:
It makes sense.
Thank you Tom for your answer and your work on open source projects.
Thank you Tom for your answer and your work on open source projects.
regards,
Sébastien Caunes
Le jeu. 30 sept. 2021 à 15:35, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Sébastien 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