Current week number for current month

Question

I used following query to get current week

select extract(week from current_timestamp) and it is showing 34 thats fine.

But how do i get the current week number of the current month.

Answer 1

You can find the first day of this week with:

trunc('week', current_date)

And the first day of the first week of this month with:

trunc('week', date_trunc('month', current_date))

Subtract the two to find the in-month weeknumber:

extract('day' from date_trunc('week', current_date) -
                   date_trunc('week', date_trunc('month', current_date))) / 7 + 1

Answer 2

Try the following

SELECT to_char('2016-05-01'::timestamp, 'W');

Answer 3

You can find week number by using day as well like:

select ((date_part('day', current_date)::integer - 1) / 7) +1;