Thanks Tom! I need to read in more detail in the future, I'd hit the function definition, and scanned to find the keyword to get WOY. I'll switch my code to ISOYEAR per the doc. Ben -----Original Message----- From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] Sent: Friday, September 25, 2015 5:01 PM To: Ben Primrose Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Extract giving wrong week of year Ben Primrose <bprimrose@xxxxxxxxxxxxx> writes: > I’m seeing some odd behavior from the extract function. > Any idea why extract would think January 3rd of next year is the 53rd > week of the year? See the fine manual ... http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT week The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year. In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It's recommended to use the isoyear field together with week to get consistent results. Depending on what you want to define a "week" as, you can probably get what you want from some other EXTRACT() property. "doy" div 7 might help, for example. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
