postgresql Get weeks of the month from date

Nur Faiz 0
Viewed 27

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

postgresql
2 Answers

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
Sund'er0

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
Stefanov.sm0
Related