Thread

1. BUG #13845: Incorrect week number

   kees.westerlaken@valuecare.nl — 2016-01-05T10:59:24Z

   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.
   
   
   
   

2. Re: BUG #13845: Incorrect week number

   Oleksandr Shulgin <oleksandr.shulgin@zalando.de> — 2016-01-05T11:09:04Z

   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
   

3. Re: BUG #13845: Incorrect week number

   Oleksandr Shulgin <oleksandr.shulgin@zalando.de> — 2016-01-05T11:11:54Z

   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.
   

4. Re: BUG #13845: Incorrect week number

   Feike Steenbergen <feikesteenbergen@gmail.com> — 2016-01-05T13:32:43Z

   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
   

5. Re: BUG #13845: Incorrect week number

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-01-05T15:10:53Z

   "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