PostgreSQL: How change date_trunc week start day from monday to another day in sql query?

Question

I want to use date_trunc function in PostgreSQL on my datetime column to aggregate data in a week.

For example I need to get number of sales each week.

The problem is date_trunc('week', datetime_column) function considers Monday as the week start day and some of my customers user different start day in calendar (like Saturday).

I tried

 SELECT date_trunc('WEEK',(time_id + interval '2 day'))- interval '2 day' AS WEEK

but it's messy and I think there must be a better way.

Answer 1

I needed a cleaner and easier way to tranc_date week with any week start day but considering provided solution I think my query is the best solution right now.

I think I can create a function to make it more readable but I still want a way to prevent performing 2 math operation for each record.

I will make my code the solution but I would be glad to know if there is a way to make my query faster.

 SELECT date_trunc('WEEK',(time_id + interval '2 day'))- interval '2 day' AS WEEK

Answer 2

try this one

select
    datetime_column
    - extract(isodow from datetime_column) + 3  -- 3 is the weekday number to which we want to truncate
    - cast(extract(isodow from datetime_column) < 3 as int) * 7  -- 3 is the weekday number to which we want to truncate
from
    <table_name>