Search code examples
postgresqldate-arithmetic

How to change start of the week in PostgreSQL

I'm trying to create a string with the week number and the first and last date on that week, like this:

'W41 04/10-10/10'

I was able to create a formula on Google Sheets that solve my problem and now I need to do this on PostgreSQL.

The problem is we use Sunday as the first day of the week on our reports and PostgreSQL uses Monday as the first day.

So for PostgreSQL Week 41 is from 05/10 to 11/10 and I need it from 04/10 to 10/10

My question is: Is there a way to change the first day on PostgreSQL too? I've created a code that creates the string just like I need, but the week number is wrong because of that definition.

Edit:

I've managed to correct the week number using this code:

ceil(((cte.data::date -((CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date - CAST
               (EXTRACT (isodow FROM  (CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date)
               as integer)
               ))+1)/7.0)

Now the week number appears just like i need, for example, 04/10 is Week 41 Now I need to get first and last date based on this, the result this far is:

Result Sample

Solution

  • After some research and thinking I used this code to get the week number like I needed

    ceil(((cte.data::date -((CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date - CAST
               (EXTRACT (isodow FROM  (CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date)
               as integer)
               ))+1)/7.0)

    and then I gave an alias of 'num' and calculated the first and last date this way:

    'W' || num ||
            to_char(to_date('20200101','YYYYMMDD')+  (num*7)-10,' DD/MM - ')  ||
            to_char(to_date('20200101','YYYYMMDD')+  (num*7)-4, 'DD/MM') as WEEK

    The final output was the week number with start and end date of the week using Sunday as the first day in the week