Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'

Hi Tom,

thanks for answering!

I commited two different patches:

-------

The first one is for the strange behaviour of to_char(), which could be
seen as a bug, I believe. As described earlier, to_char() with the
'WW-D' pattern could return wrong week numbers.

The non-ISO week number is defined for weeks beginning with Sundays and
ending with Saturdays. The first week of the year is the week with
January, 1st.

For example:

postgres=# SELECT to_char('1997-01-01'::date, 'YYYY-WW-D');
to_char
---------
1997-01-4
(1 row)

1997-01-01 was a Wednesday. So the first week in 1997 was from Jan 1st
to Jan 4th (Saturday). Week 2 started on Jan 5th. But to_char() gives
out week number 1 until Tuesday (!), Jan 7th.

postgres=# SELECT to_char('1997-01-07'::date, 'YYYY-WW-D');
to_char
---------
1997-01-3
(1 row)

After that, on Jan 8th, the output switches from 01-3 to 02-4, which
makes no sense in my personal opinion. The week number should be
consistent from Sun to Sat and should not switch during any day in the
week. Furthermore, it is not clear why Jan 7th should return an earlier
week day (3) than Jan 1st (4).

The bug is, that the calculation of the week number only considers the
number of days of the current year. But it ignores the first week day,
which defines an offset. This has been fixed in the first patch.

-------

Second patch:

As you stated correctly, this is not a bug fix, because the current
behaviour is documented and it works as the documentation states. I
tried to describe my confusion in the very first post of this thread:

I was wondering why the D part is not recognized in the non-ISO week
pattern while the ISO day is working very well. Although this is
documented, there could be a chance that this simply was not implemented
right now - so I tried.

The main aspect, I believe, is, that to_date() or to_timestamp() is some
kind of "back" operation of the to_char() function. So, a new definition
simply should recognize the week day as the to_char() function does,
instead of setting the day part fix to any number (please see the
examples in the very first post for that).

-------

Combining both patches, the to_char() fix and the to_date() change, it
is possible to calculate the non-ISO week pattern in both directions:

SELECT to_date(to_char(anydate, 'YYYY-WW-D'), 'YYYY-WW-D')

would result in "anydate". Currently it does not:

postgres=# SELECT to_date(to_char('1997-01-07'::date, 'YYYY-WW-D'),
'YYYY-WW-D')
to_char
---------
1997-01-01
(1 row)

postgres=# SELECT to_char(to_date('1997-01-07', 'YYYY-WW-D'),
'YYYY-WW-D')
to_char
---------
1997-01-04
(1 row)

On the other hand, the ISO week calculations work as expected,
especially the there-and-back operation results in the original value:

postgres=# SELECT to_date(to_char('1997-01-07'::date, 'IYYY-IW-ID'),
'IYYY-IW-ID')
to_char
---------
1997-01-07
(1 row)

postgres=# SELECT to_char(to_date('1997-01-07', 'IYYY-IW-ID'),
'IYYY-IW-ID')
to_char
---------
1997-01-7
(1 row)

The only difference between ISO and non-ISO weeks is the beginning on
Mondays and the definition of the first week. But this cannot be the
reason why one operation results in right values (comparing with a
calendar) and the other one does not.

Does this explanation make it clearer?