Обсуждение: BUG #13845: Incorrect week number
The following bug has been logged on the website:
Bug reference: 13845
Logged by: Kees Westerlaken
Email address: kees.westerlaken@valuecare.nl
PostgreSQL version: 9.2.10
Operating system: Linux
Description:
2016 is a year where week numbers (US) differ from ISO.
On 1 januari 2016 it works OK.
select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYYWW')
produces 201601, while
select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYYIW')
produces 201553.
However on 4 januari 2016
select to_char(to_date('20160104', 'YYYYMMDD'), 'YYYYWW')
produces 201601, which should be 201602 !!
And surprisingly on 8 januari 2016, which is in the same week as 4 januari
select to_char(to_date('20160108', 'YYYYMMDD'), 'YYYYWW')
produces 201602, which is correct.
On Tue, Jan 5, 2016 at 11:59 AM, <kees.westerlaken@valuecare.nl> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 13845
> Logged by: Kees Westerlaken
> Email address: kees.westerlaken@valuecare.nl
> PostgreSQL version: 9.2.10
> Operating system: Linux
> Description:
>
> 2016 is a year where week numbers (US) differ from ISO.
>
> On 1 januari 2016 it works OK.
> select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYYWW')
> produces 201601, while
> select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYYIW')
> produces 201553.
>
It gets worse on HEAD:
postgres=# select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYY:IW');
to_char
---------
2016:53
(1 row)
This is something close to 2017 already.
--
Alex
On Tue, Jan 5, 2016 at 12:09 PM, Shulgin, Oleksandr <
oleksandr.shulgin@zalando.de> wrote:
> On Tue, Jan 5, 2016 at 11:59 AM, <kees.westerlaken@valuecare.nl> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 13845
>> Logged by: Kees Westerlaken
>> Email address: kees.westerlaken@valuecare.nl
>> PostgreSQL version: 9.2.10
>> Operating system: Linux
>> Description:
>>
>> 2016 is a year where week numbers (US) differ from ISO.
>>
>> On 1 januari 2016 it works OK.
>> select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYYWW')
>> produces 201601, while
>> select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYYIW')
>> produces 201553.
>>
>
> It gets worse on HEAD:
>
> postgres=# select to_char(to_date('20160101', 'YYYYMMDD'), 'YYYY:IW');
> to_char
> ---------
> 2016:53
> (1 row)
>
> This is something close to 2017 already.
>
Scratch that, I'm confused about YYYY format, has to use IYYY instead.
If I read and understand the documentation correctly, WW starts on the
first day of the year, therefore January 1 - 7 is week 01.
The following query seems to confirm this on 9.4, I'm looking for the week
number for day 7 of the year:
# # select to_char(day, 'YYYYWW') as week, to_char(day, 'IYYYIW') as
isoweek from generate_series('2008-01-07','2020-01-07', interval '1 year')
as series(day);
week | isoweek
--------+---------
200801 | 200802
200901 | 200902
201001 | 201001
201101 | 201101
201201 | 201201
201301 | 201302
201401 | 201402
201501 | 201502
201601 | 201601
201701 | 201701
201801 | 201801
201901 | 201902
202001 | 202002
(13 rows)
According to wikipedia, the US weeks starts on a Sunday, it would already
have resulted in differences when using the WW format for weeks in the
past, as WW starts on day 1, whether it is Sunday or Wednesday.
regards,
Feike
"Shulgin, Oleksandr" <oleksandr.shulgin@zalando.de> writes:
> Scratch that, I'm confused about YYYY format, has to use IYYY instead.
Exactly. There's no bug here, it's just that you have to use IYYY
together with IW if you want sensible results.
Per the fine manual:
Caution: While to_date will reject a mixture of Gregorian and ISO
week-numbering date fields, to_char will not, since output format
specifications like YYYY-MM-DD (IYYY-IDDD) can be useful. But avoid
writing something like IYYY-MM-DD; that would yield surprising results
near the start of the year.
regards, tom lane