pgsql中实现按周统计，计算日期是周几

EXTRACT(DOW FROM CURRENT_DATE) 函数的返回值，0表示星期日，6表示星期六

测试数据

select 
extract(DOW FROM cast('20220630' as TIMESTAMP)),
extract(DOW FROM cast('20220701' as TIMESTAMP)),
extract(DOW FROM cast('20220702' as TIMESTAMP)),
extract(DOW FROM cast('20220703' as TIMESTAMP)),
extract(DOW FROM cast('20220704' as TIMESTAMP)),
extract(DOW FROM cast('20220705' as TIMESTAMP)),
extract(DOW FROM cast('20220706' as TIMESTAMP))

 

select current_date||'/'||
 (case extract(DOW FROM current_date) 
when 1 then 'Monday'
when 2 then 'Tuesday'
when 3 then 'Wednesday'
when 4 then 'Thursday'
when 5 then 'Friday'
when 6 then 'Saturday'
when 0 then 'Sunday' end) as date