postgresql Get weeks of the month from date-CodePudding

I have this postgresql query

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'

and the results are

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

What I need for the week column is number 2 because the date (11) is in the 2nd weeks of august. Any help will be great. Thank you

CodePudding user response：

I use date part for these extractions

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 ;

OUTPUT :

currentweekofthemonth   weekoftheyear   mon     yr
2   32  8   2022

CodePudding user response：

Subtract the week number of the target date from the week number of the first day of the month of the target date and add one. Here is your query with this modification.

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';

Demo

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

-- yields 2