如何将DATE_TRUNC提前10天

Question

我有一个名为payments的表，其中包含客户对某些特定的所有付款的信息如下所示的时间段:

Client_id             sum                  date
2345                   50$             2020-11-20
4556                   20$             2020-12-01
3434                   30$             2020-10-07
5667                   10$             2020-11-15
2346                   25$             2020-12-06
5667                   60$             2020-12-16

并且我需要找到所有客户的付款间隔为10天(某段时间))，如:

Client_id                     sum            Interval_of_payments
2345                          50$          2020-11-13 to 2020-11-22
4556                          20$          2020-12-01 to 2020-12-10
2346                          25$          2020-12-03 to 2020-12-12

但是一天并不总是从一个月的第一天开始。

我写了查询，但它返回week:

select payments.client_id, sum(payments.sum), DATE_TRUNC('week', date)
from payments 
where payments.date between '2020-11-01' and '2020-12-31'
group by 1,3 

总而言之，我想让它像DATE_TRUNC('10 days')一样工作

Answer 1

可以使用简单的算术:

create function date_trunc(start_date date, period int, the_date date)
returns date
stable strict
language sql
as $$
select the_date - ((the_date - start_date) % period)
$$;

让我们测试一下:

select
date_trunc('2020-11-02', 10, '2020-11-05'),
date_trunc('2020-11-02', 10, '2020-11-20'),
date_trunc('2020-11-02', 10, '2020-12-06');
┌────────────┬────────────┬────────────┐
│ date_trunc │ date_trunc │ date_trunc │
├────────────┼────────────┼────────────┤
│ 2020-11-02 │ 2020-11-12 │ 2020-12-02 │
└────────────┴────────────┴────────────┘

请注意，对于2020-12-06，它应该是2020-12-02，而不是像您的示例中的2020-12-03。

Answer 2

计算任意日期与标准日期之间的差值，然后使用算术进行计算。

以下是周期计数器以及周期的开始和结束日期的计算:

select p.*,
(p.date - v.date) / 10 as period_counter,
v.date + ( (p.date - v.date) / 10 ) * interval '10 day') as period_start,
v.date + (9 +  (p.date - v.date) / 10 ) * interval '10 day') as period_end
from payments p cross join
(values ('2020-11-02'::date)) v(date);

这似乎回答了你实际问的问题。