From: | Sébastien Caunes <sebastien(at)pixseed(dot)fr> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Bug report function to_date should reject HH24:MI:SS formatting options |
Date: | 2021-09-30 14:10:52 |
Message-ID: | CANtq+vR=FdT0_a-FZ73mTpGThZemd=cDJ-KrpUUzOh33_3u=kw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)sss(dot)pgh(dot)pa(dot)us> a écrit :
> =?UTF-8?Q?S=C3=A9bastien_Caunes?= <sebastien(at)pixseed(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2021-09-30 20:54:19 | BUG #17206: the function array_cat(anyarray, anyarray) does not exist |
Previous Message | Tom Lane | 2021-09-30 13:35:18 | Re: Bug report function to_date should reject HH24:MI:SS formatting options |