pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug report function to_date should reject HH24:MI:SS formatting options
3+ messages / 2 participants
[nested] [flat]
* Bug report function to_date should reject HH24:MI:SS formatting options
@ 2021-09-30 09:03 Sébastien Caunes <sebastien@pixseed.fr>
2021-09-30 13:35 ` Re: Bug report function to_date should reject HH24:MI:SS formatting options Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Sébastien Caunes @ 2021-09-30 09:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug report function to_date should reject HH24:MI:SS formatting options
2021-09-30 09:03 Bug report function to_date should reject HH24:MI:SS formatting options Sébastien Caunes <sebastien@pixseed.fr>
@ 2021-09-30 13:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-30 14:10 ` Re: Bug report function to_date should reject HH24:MI:SS formatting options Sébastien Caunes <sebastien@pixseed.fr>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-09-30 13:35 UTC (permalink / raw)
To: Sébastien Caunes <sebastien@pixseed.fr>; +Cc: pgsql-bugs@lists.postgresql.org
=?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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug report function to_date should reject HH24:MI:SS formatting options
2021-09-30 09:03 Bug report function to_date should reject HH24:MI:SS formatting options Sébastien Caunes <sebastien@pixseed.fr>
2021-09-30 13:35 ` Re: Bug report function to_date should reject HH24:MI:SS formatting options Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-09-30 14:10 ` Sébastien Caunes <sebastien@pixseed.fr>
0 siblings, 0 replies; 3+ messages in thread
From: Sébastien Caunes @ 2021-09-30 14:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
It makes sense.
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 :
> =?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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-09-30 14:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-30 09:03 Bug report function to_date should reject HH24:MI:SS formatting options Sébastien Caunes <sebastien@pixseed.fr>
2021-09-30 13:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-30 14:10 ` Sébastien Caunes <sebastien@pixseed.fr>
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