CODEWITHSUNDEEP
postgresql
user17911
user17911

Reputation: 1351

How to find the week number per current month in PostgreSQL?

My working environment

PostgreSQL version: 10.9 (64 bits)
OS: Windows 10 (64 bits)

I need to find the week number of a given date in the current month. So unlike the ISO week number which is computed from the beginning of the year, here the computation is done from the beginning of the current month and therefore the result is a number between 1 and 5. Here are a few examples for June 2020:

Date in the current month             Week number per current month
==========================           ===============================
2020-06-01                   ----->   1
2020-06-10                   ----->   2
2020-06-19                   ----->   3
2020-06-23                   ----->   4
2020-06-23                   ----->   5

I was reading the online documentation: Date/Time Functions and Operators It seems that there is no function providing directly what I'm looking for. So after a few successful tests, here is the solution that I found:

select 
    extract('week' from current_date) - 
    extract('week' from date_trunc('month', current_date)) 
    + 1;

I consider myself to be rather a beginner in using date functions, so just to make sure that I'm on the right track, do you think that this solution is correct? As I said, after a few tests it seems to me that it gets the job done.

Upvotes: 1

Views: 1060

Answers (1)

user330315
user330315

Reputation:

The to_char() method offers such a feature:

W - week of month (1-5) (the first week starts on the first day of the month)

select to_char(current_date, 'W');

It returns a string value, but that can easily be cast to a number.

Upvotes: 3

Related Questions