[gtrubetskoy]

The week example is a tad misleading, 2017-01-01 is a Sunday, which in some/most? countries is the first day of the week.

If the date were 2016-01-01 and you compared it with what week Postgres thinks it is, you'd get:

  SELECT date_part('week', '2016-01-01'::date);
   date_part 
  -----------
          53
  (1 row)

This is because 2016-01-01 is still the 53rd week of 2015.

Edit: Actually, 2017-01-01 is week 52 according to Postgres, probably because it uses Monday as the first day of the week.

[jontro]

Probably because it's using ISO-8601 week numbers. https://en.wikipedia.org/wiki/ISO_week_date

[elmigranto]

> Sunday, which in some/most? countries is the first day of the week.

Just like imperial system, only a couple of weirdos do that.

[lobster_johnson]

Postgres uses the ISO definition of week for "week", which starts on Monday. For "dow", it uses the American week definition.

[anarazel]

isodow for the sane definition ;)