获取日期范围内星期几的日期 [英] Get dates of a day of week in a date range

问题描述

我需要PostgreSQL中的一个函数，该函数接受一个日期范围，并返回该日期范围内的日期，该日期范围是星期一.任何人都知道如何做到这一点?

I need a function in PostgreSQL that accepts a date range and returns the dates inside the date range that are Mondays. Anybody have an idea how this could be done?

推荐答案

最有效的方法应该是找到第一个星期一，并以7天为步长生成一系列:

The most efficient way should be to find the first Monday and generate a series in steps of 7 days:

CREATE OR REPLACE FUNCTION f_mondays(dr daterange)
  RETURNS TABLE (day date) AS
$func$
SELECT generate_series(a + (8 - EXTRACT(ISODOW FROM a)::int) % 7
                     , z
                     , interval '7 days')::date
FROM  (
   SELECT CASE WHEN lower_inc(dr) THEN lower(dr) ELSE lower(dr) + 1 END AS a
        , CASE WHEN upper_inc(dr) THEN upper(dr) ELSE upper(dr) - 1 END AS z
   ) sub
$func$ LANGUAGE sql;

• 子查询提取范围的开始( a )和结束( z )，并针对包含范围和排除范围进行了调整

  • The subquery extracts start (a) and end (z) of the range, adjusted for inclusive and exclusive bounds with range functions.

    表达式(8-EXTRACT(ISODOW FROM a):: int)％7 返回直到下一个星期一的天数. 0 (如果已经是星期一).有关 EXTRACT()的手册.

    The expression (8 - EXTRACT(ISODOW FROM a)::int) % 7 returns the number of days until the next monday. 0 if it's Monday already. The manual about EXTRACT().

    generate_series() 可以迭代任何给定的间隔-在这种情况下为7天.结果是一个时间戳，因此我们将其强制转换为 date .

    generate_series() can iterate any given interval - 7 days in this case. The result is a timestamp, so we cast to date.

    仅生成范围内的星期一，不需要 WHERE 子句.

    Only generates Mondays in the range, no WHERE clause needed.

    致电:

    SELECT day FROM f_mondays('[2014-04-14,2014-05-02)'::daterange);
    

    返回:

    day
    ----------
    2014-04-14
    2014-04-21
    2014-04-28
    

    SQL提琴.

    这篇关于获取日期范围内星期几的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！