postgresql从日期开始获取每月的周数

Question

我有这个postgresql查询

SELECT pick.min_date, extract('week' from pick.min_date) as week FROM account_invoice inv 
left join stock_picking pick on inv.origin=pick.name 
WHERE inv.number ='INV/2022/17359'

结果是

min_date            | week
2022-08-11 02:01:00 | 32

我需要周栏的数字是2，因为日期(11(在8月的第二周。任何帮助都会很棒。谢谢

Answer 1

从目标日期当月第一天的周数中减去目标日期的周数，再加一。这是您修改后的查询。

SELECT pick.min_date, 
extract('week' from pick.min_date) - 
extract('week' from date_trunc('month', pick.min_date)) + 1 as week 
FROM account_invoice inv 
left join stock_picking pick on inv.origin=pick.name 
WHERE inv.number ='INV/2022/17359';

演示

SELECT  
extract('week' from '2022-08-11'::date) - 
extract('week' from date_trunc('month', '2022-08-11'::date)) + 1;
-- yields 2

Answer 2

我使用日期部分进行这些提取

select 
((date_part('day', dt::date)::integer - 1) / 7) +1 as currentweekofthemonth, 
date_part('week', dt::date) AS weekoftheyear,
date_part('month', dt::date)  AS mon,
date_part('year', dt::date)  AS yr from 


(select '2022-08-11 02:01:00' as dt) as drt ;

输出：

currentweekofthemonth   weekoftheyear   mon     yr
2   32  8   2022