postgresql - 在 postgresql 中提取周数的匹配年份

Question

在 postgresql 中，extract(week from '2014-12-30'::timestamp) 给出了 2015 年的第 1 周。如何提取与周数对应的相关年份？使用 extract(year ... 给出 2014

Answer 1

因为 week 是 ISO 定义的周，所以您需要 isoyear:

#= select extract(isoyear from '2014-12-30'::timestamp);
 date_part
-----------
      2015
(1 row)

来自docs :

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.