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

Hi,

I fixed the described issue in the to char() function.

The output of the current version is:

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

postgres=# SELECT to_char('1997-02-03'::date, 'YYYY-WW-D');
to_char
---------
1997-05-2
(1 row)

postgres=# SELECT to_char('1997-02-10'::date, 'YYYY-WW-D');
to_char
---------
1997-06-2
(1 row)

As you can see, the week day of the Feb 3rd - which is two days AFTER
Feb 1st - yields in a result which is 5 days BEFORE the earlier date,
which obviously cannot be. Furthermore, using the Gregorian calendar,
Feb 3rd is in week 6. So, the Feb 10th cannot be in week 6 as well.

The bug was, that the week day of Jan 1st was not considered in the
calculation of the week number. So, a possible offset has not been set.

New output:

postgres=# SELECT to_char('1997-02-03'::date, 'YYYY-WW-D');
to_char
---------
1997-06-2
(1 row)

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

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

-------------------

Furthermore I adjusted the to_date() functionality for the WW-D pattern
as well. As said before in the thread, I know, ignoring the D part is
known and documented, but I think, if the ISO format recognizes the day
part, the non-ISO format should as well - especially when the "back"
operation does as well (meaning to_char()):

Output in the current version:

postgres=# SELECT to_date('2019-1-1', 'YYYY-WW-D');
to_date
------------
2019-01-01
(1 row)

postgres=# SELECT to_date('2019-1-2', 'YYYY-WW-D');
to_date
------------
2019-01-01
(1 row)

postgres=# SELECT to_date('2019-1-3', 'YYYY-WW-D');
to_date
------------
2019-01-01
(1 row)

postgres=# SELECT to_date('2019-1-7', 'YYYY-WW-D');
to_date
------------
2019-01-01
(1 row)

postgres=# SELECT to_date('2019-2-1', 'YYYY-WW-D');
to_date
------------
2019-01-08
(1 row)

New output:

postgres=# SELECT to_date('2019-1-1', 'YYYY-WW-D');
to_date
------------
2018-12-30
(1 row)

postgres=# SELECT to_date('2019-1-2', 'YYYY-WW-D');
to_date
------------
2018-12-31
(1 row)

postgres=# SELECT to_date('2019-1-3', 'YYYY-WW-D');
to_date
------------
2019-01-01
(1 row)

postgres=# SELECT to_date('2019-1-7', 'YYYY-WW-D');
to_date
------------
2019-01-05
(1 row)

postgres=# SELECT to_date('2019-2-1', 'YYYY-WW-D');
to_date
------------
2019-01-06
(1 row)

I added the patch as plain text attachment. It contains the code and, of
course, the regression tests. Some existing tests failed, because they
worked with the old output. I have changed their expected output.

Hope you'll find it helpful.

Best regards,
Mark Lorenz