为一年中的某一周生成日期

I'm fairly new to PostgreSQL and recently on my project I've encountered problem with retrieving all records for specific week of year. The main problem is of course with border weeks (1, and 52/53).

我对PostgreSQL非常陌生，最近在我的项目中，我遇到了检索一年中的特定星期的所有记录的问题。主要的问题当然是边境周(1,52 /53)。

For this I've wrote a function:

为此我写了一个函数:

CREATE OR REPLACE FUNCTION week_dates(_week integer, _year integer) RETURNS date[] AS $$
DECLARE
    result date[];
BEGIN
    WITH RECURSIVE dates(wdate) AS (
        SELECT MAX(date) AS wdate FROM time WHERE woy = _week AND year = _year AND dow > 3
        UNION ALL
        SELECT wdate-1 AS wdate FROM dates WHERE EXTRACT(WEEK from (wdate-1)) = _week 
    ),
    dates2(wdate) AS (
        SELECT MAX(wdate) AS wdate FROM dates 
        UNION ALL
        SELECT wdate+1 AS wdate FROM dates WHERE EXTRACT(WEEK from (wdate+1)) = _week 
    ),
    sorted AS ((SELECT * FROM dates) UNION (SELECT * FROM dates2) ORDER BY wdate ASC)
    -- sorted AS (SELECT wdate FROM dates ORDER BY wdate ASC)
    SELECT array_agg(wdate) INTO result FROM sorted;
    -- SELECT wdate FROM sorted;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

And the usage of it is, eg.:

它的用法是。

SELECT * FROM "some_report_cache_table" WHERE "date" = ANY(week_dates(1, 2013));

Is there a better/faster/simpler solution for this (maybe some built-in functionality)?

是否有更好/更快/更简单的解决方案(可能是一些内置功能)?

I'm using PostgreSQL 9.2 and by week I mean ISO week of year (starts on monday)

我用的是PostgreSQL 9.2，我指的是一年中的ISO周(周一开始)

2 个解决方案

#1

Much simpler, yet:

简单得多,但:

CREATE OR REPLACE FUNCTION week_dates(_week integer, _year integer)
  RETURNS SETOF date AS
$func$
SELECT date_trunc('week', ($2::text || '-1-4')::timestamp)::date
       + 7 * ($1 - 1)  -- fix off-by-one
       + generate_series (0,6)
$func$ LANGUAGE sql;

The major point is that you can add integer to date to increment days in PostgreSQL.
date_trunc() returns a timestamp, so we need to cast to date another time.

主要的一点是，可以在PostgreSQL中添加到日期的整数来增加天数。date_trunc()返回一个时间戳，因此需要对另一个时间进行强制转换。

I start with the 4th of January because (quoting the manual here):

我从1月4日开始，因为(在这里引用手册):

By definition (ISO 8601), weeks start on Mondays and the first week of a year contains January 4 of that year.

根据定义(ISO 8601)，周一开始数周，一年的第一个星期包含当年的1月4日。

The function happily accepts impossible weeks like -1 or 55 and returns a somewhat reasonable result. To disallow that, I would make it a plpgsql function and check the input values.

函数愉快地接受不可能的周数，如-1或55，并返回某种合理的结果。为了避免这种情况，我将它设置为plpgsql函数并检查输入值。

This returns a set instead of an array. It's trivial to make it an array instead (SELECT ARRAY(SELECT ...). But this should be faster. Your query could look like this:

它返回一个集合而不是数组。让它成为一个数组是很简单的(选择数组(选择…)但这应该更快。您的查询可以如下:

SELECT *
FROM   some_report_cache_table
JOIN   week_dates(1, 2013) w("date") USING ("date")

Aside: You shouldn't use date as column name, since it is a reserved word in SQL.

旁白:您不应该使用日期作为列名，因为它是SQL中的保留词。

#2

I think this is simpler. It will return all days of a given week in a given year.

我认为这更简单。在给定的一年里，它将在给定的一周内返回所有的天。

create or replace function week_dates(_week integer, _year integer)
returns setof date as $$

    with first_day_of_first_week as (
        select distinct date_trunc('week', d)::date
        from generate_series(
            to_date(_year::text, 'YYYY'),
            to_date(_year::text, 'YYYY') + 3,
            '1 day'
        ) s(d)
        where extract(week from d) = 1
    )
    select
        (select first_day_of_first_week)
        + (_week - 1) * 7
        + generate_series(0, 6) "day" 
;

$$ language sql;

select * from week_dates(1, 2012) s("day");
    day     
------------
 2012-01-02
 2012-01-03
 2012-01-04
 2012-01-05
 2012-01-06
 2012-01-07
 2012-01-08

#1