Re: Extract giving wrong week of year

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, September 25, 2015 5:01 PM
To: Ben Primrose
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Extract giving wrong week of year

Ben Primrose <bprimrose(at)tracelink(dot)com> 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