postgresql - 我如何从 postgresql 中的两个日期之间获取 recored week wise？

Question

我正在使用这个查询来明智地获取数据

SELECT 
     (count( server_time::timestamp::date)) , 
     server_time::timestamp::date 
FROM 
     complaint_details_v2 
WHERE 
     server_time between '2018/10/03' and '2018/10/11' 
GROUP BY 
     server_time::timestamp::date 
ORDER BY 
     server_time ASC

但我想在两个日期之间按周而不是按天更改上述查询。

Answer 1

您可以通过日期算术将日期分成七组。

SELECT 
     (count( server_time::timestamp::date)) , 
     min(server_time::timestamp::date) as "week starting"
FROM 
     complaint_details_v2 
WHERE 
     server_time between '2018/10/03' and '2018/10/11' 
GROUP BY 
     floor((server_time::timestamp::date  - '2018/10/03'::date)/7)
ORDER BY 
     2 ASC

另一种选择是分组表达式 date_trunc(week,server_time) 但这会将您绑定(bind)到 ISO 周数