按周分组，并将星期六视为一周的开始日

Question

我想查询我的 postgresql 数据并按周对结果进行分组，所以我使用以下查询：

select
date_trunc ('week', date_column) as week,
sum (orders) as orders_count
from database
group by week

但它使用星期一作为一周的开始日，而我希望我的星期像"星期六 ->星期五"。我怎样才能做到这一点？

Answer 1

只需减去两天，你就在星期六降落：

select
date_trunc('week', date_column)::date - 2 as week,
sum (orders) as orders_count
from the_table
group by week

Answer 2

您可以只偏移两天，如下所示：

select
date_trunc ('week', date_column + interval '2 days') - interval '2 days' as week,
sum (orders) as orders_count
from database
group by week