Question 114665 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql Get weeks of the month from date

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search