sql - 生成一年中特定周的日期

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

为此，我编写了一个函数:

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;

它的用法是，例如:

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

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

我使用的是 PostgreSQL 9.2，按周我的意思是一年中的 ISO 周(从星期一开始)

最佳答案

更简单，但:

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;

主要的一点是您可以将 integer 添加到 date 以增加 PostgreSQL 中的天数。date_trunc() 返回一个 timestamp ，所以我们需要再次转换为 date 。

我从 1 月 4 日开始，因为( quoting the manual here ):



该函数愉快地接受不可能的周，如 -1 或 55 并返回一个有点合理的结果。为了禁止这种情况，我会将其设为 plpgsql 函数并检查输入值。

这将返回一个 集 而不是一个数组。将其改为数组很简单( SELECT ARRAY(SELECT ... )。但这应该更快。您的查询可能如下所示:

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

旁白:您不应该使用 date 作为列名，因为它是 reserved word in SQL 。

关于sql - 生成一年中特定周的日期，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16604267/