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.

Lengthy

This looks like a good solution. I don't see any cleaner way. – Disposure

I fully agree with @DenysSéguret – Fourpence

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

Lengthy

So you marked your own response as a solution even when it's the same as in your quesdtion? – Moiety

It has been over a year since I asked the question and I couldn't find a cleaner way. If you have a better way please add an answer! @Moiety – Lengthy

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>

Scissure

Thanks but my query is more efficient than yours. I mean I wanted to prevent 2 extra math function not to add extra functions – Lengthy

Ok, this one just works for any day of the week to which you want cut the date (just replace 3 for integer between 1 and 7) – Scissure

Answer 3

try % (remainder of division)

select i,
       now() + make_interval(days := i) "timestamp",
       extract(dow from now() + make_interval(days := i)) day_of_week,
       (extract(dow from now() + make_interval(days := i))::int+3)%7 day_of_week_shift
    from
        generate_series(0, 6) i

Diphthongize

Answer 4

I don't know about PostgreSQL, but in BigQuery it's as simple as :

SELECT DATE_TRUNC(DATE, WEEK(WEDNESDAY))

*replace wednesday with any other day you want your starting day to be.

Lighter

Pardon me, but I don't understand how BigQuery can help when the question is about PostgreSQL. – Oberhausen