两个日期之间的周数 [英] Weeks between two dates

问题描述

我正试图将两个日期变成一系列记录。日期之间的每周记录。

I'm attempting to turn two dates into a series of records. One record for each week between the dates.

另外，如果范围在周中开始或结束，则应使用原始的开始和结束日期来裁剪一周。我还假设一周从星期一开始。

Additionally the original start and end dates should be used to clip the week in case the range starts or ends mid-week. I'm also assuming that a week starts on Monday.

开始日期为： 2018年9月9日，结束日期为 2018年9月27日，我想检索以下结果：

With a start date of: 05/09/2018 and an end date of 27/09/2018 I would like to retrieve the following results:

| # | Start Date   | End date     |
|---------------------------------|
| 0 | '05/09/2018' | '09/09/2018' |
| 1 | '10/09/2018' | '16/09/2018' |
| 2 | '17/09/2018' | '23/09/2018' |
| 3 | '24/09/2018' | '27/09/2018' |

我已经取得了一些进展-目前，我可以获得日期范围之间的总周数

I have made some progress - at the moment I can get the total number of weeks between the date range with:

SELECT (
  EXTRACT(
      days FROM (
                  date_trunc('week', to_date('27/09/2018', 'DD/MM/YYYY')) -
                  date_trunc('week', to_date('05/09/2018', 'DD/MM/YYYY'))
                ) / 7
  ) + 1
) as total_weeks;

以上SQL的总周数将返回4。这就是我所困的地方，从整数到实际结果集。

Total weeks will return 4 for the above SQL. This is where I'm stuck, going from an integer to actual set of results.

推荐答案

窗口函数是您的朋友：

SELECT week_num,
       min(d) AS start_date,
       max(d) AS end_date
FROM (SELECT d,
             count(*) FILTER (WHERE new_week) OVER (ORDER BY d) AS week_num
      FROM (SELECT DATE '2018-09-05' + i AS d,
                   extract(dow FROM DATE '2018-09-05'
                                    + lag(i) OVER (ORDER BY i)
                          ) = 1 AS new_week
            FROM generate_series(0, DATE '2018-09-27' - DATE '2018-09-05') AS i
           ) AS week_days
     ) AS weeks
GROUP BY week_num
ORDER BY week_num;

 week_num | start_date |  end_date  
----------+------------+------------
        0 | 2018-09-05 | 2018-09-09
        1 | 2018-09-10 | 2018-09-16
        2 | 2018-09-17 | 2018-09-23
        3 | 2018-09-24 | 2018-09-27
(4 rows)

这篇关于两个日期之间的周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！