代码之家 › 专栏 › 技术社区 › Royal Wares

两个日期之间的周

postgresql sql

Royal Wares · 技术社区 · 6 年前

我想把两个约会变成一系列的记录。两个日期之间每周记录一次。

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

开始日期: 05/09/2018 结束日期为 27/09/2018 我想检索以下结果:

| # | 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' |

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

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。这就是我遇到的困难,从整数到实际结果集。

3 回复 | 直到 6 年前

Laurenz Albe 6 年前

窗口功能是您的朋友:

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)

Gordon Linoff 6 年前

使用 generate_series() :

select gs.*
from generate_series(date_trunc('week', '2018-09-05'::date),
                     '2018-09-27'::date,
                     interval '1 week'
                    ) gs(dte)

Royal Wares 6 年前

最后,我扩展了戈登的解决方案,以达到以下目的,不过劳伦茨的回答稍微简洁一些。

select
  (
    case when (week_start - interval '6 days' <= date_trunc('week', '2018-09-05'::date)) then '2018-09-05'::date else week_start end
  ) as start_date,
  (
    case when (week_start + interval '6 days' >= '2018-09-27'::date) then '2018-09-27'::date else week_start + interval '6 days' end
  ) as end_date
from generate_series(
         date_trunc('week', '2018-09-05'::date),
         '2018-09-27'::date,
          interval '1 week'
      ) gs(week_start);