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

Поиск
Список
Период
Сортировка
От Mark Lorenz
Тема Re: Created feature for to_date() conversion using patterns'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Дата
Msg-id 75be897662abb117e54a9db715d760a7@four-two.de
обсуждение исходный текст
Ответ на Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
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?



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Session WAL activity
Следующее
От: Andres Freund
Дата:
Сообщение: Re: Clarifying/rationalizing Vars' varno/varattno/varnoold/varoattno