Possibly misleading documentation of Template Patterns for Date/Time Formatting

David Kubecka <davidkubecka366@gmail.com> writes:

on the official docs
https://www.postgresql.org/docs/9.6/functions-formatting.html see the table
9-24 and Pattern "Q". The doc (for version 9.6) says:

quarter (ignored by to_date and to_timestamp)

All the later versions of the doc (10, 11, 12) miss the "ignored" note

It's still there, just further down:

* In to_timestamp and to_date, weekday names or numbers (DAY, D, and
related field types) are accepted but are ignored for purposes of
computing the result. The same is true for quarter (Q) fields.

I think this was changed because we noticed that the docs failed to point
out the issue for weekday fields, and cramming similar annotations into
their already-long table entries didn't make sense. So the info got moved
to the commentary below.

regards, tom lane

On Friday, April 17, 2020, David Kubecka <davidkubecka366@gmail.com> wrote:

Hi,

on the official docs https://www.postgresql.org/
docs/9.6/functions-formatting.html see the table 9-24 and Pattern "Q".
The doc (for version 9.6) says:

quarter (ignored by to_date and to_timestamp)

All the later versions of the doc (10, 11, 12) miss the "ignored" note
leading the user to think that it should work but it doesn't, at least on
12.1:

# select TO_DATE( '2012-4', 'YYYY-Q' );
to_date
------------
2012-01-01
(1 row)

Is this an expected behaviour, i.e. the documentation is just wrong or it
really should work?

It was moved to the usage notes section, and expanded to be more correct.

n to_timestamp and to_date, weekday names or numbers (DAY, D, and related
field types) are accepted but are ignored for purposes of computing the
result. The same is true for quarter (Q) fields.

David J.

pá 17. 4. 2020 v 17:52 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

David Kubecka <davidkubecka366@gmail.com> writes:

on the official docs
https://www.postgresql.org/docs/9.6/functions-formatting.html see the

table

9-24 and Pattern "Q". The doc (for version 9.6) says:

quarter (ignored by to_date and to_timestamp)

All the later versions of the doc (10, 11, 12) miss the "ignored" note

It's still there, just further down:

* In to_timestamp and to_date, weekday names or numbers (DAY, D, and
related field types) are accepted but are ignored for purposes of
computing the result. The same is true for quarter (Q) fields.

I think this was changed because we noticed that the docs failed to point
out the issue for weekday fields, and cramming similar annotations into
their already-long table entries didn't make sense. So the info got moved
to the commentary below.

I see. I wonder if it wouldn't be better e.g. to reference this info from
all the ignored fields in the table through an asterisk comment just under
the table (thus making the list of unsuppored patterns even more explicit).
Just an idea, It's definitely not an easy task to make a good documentation
:-)

Anyway, could you shed some light if there's any particular reason why TO_DATE(
'2012-4', 'YYYY-Q' ) isn't supported?

David Kubecka <davidkubecka366@gmail.com> writes:

Anyway, could you shed some light if there's any particular reason why TO_DATE(
'2012-4', 'YYYY-Q' ) isn't supported?

You can't compute a specific date from that. Nor does it lend itself to
combination with other fields to reach a specific date --- adding DD
doesn't do it, and if you add MM, you might as well not bother with Q.

regards, tom lane

ne 19. 4. 2020 v 15:42 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

David Kubecka <davidkubecka366@gmail.com> writes:

Anyway, could you shed some light if there's any particular reason why

TO_DATE(

'2012-4', 'YYYY-Q' ) isn't supported?

You can't compute a specific date from that. Nor does it lend itself to
combination with other fields to reach a specific date --- adding DD
doesn't do it, and if you add MM, you might as well not bother with Q.

I think you very well could if it works e.g. for months: TO_DATE( '2012-4',
'YYYY-MM' ) returns first date of the specified month, so Q could behave
similarly.

Anyway, this is obviously not a big issue, just a minor inconvenience ;-)