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.

This looks like a good solution. I don't see any cleaner way. — Denys Séguret, Jul 30 '19 at 13:47

score 3 · Answer 1 · answered Jul 31 '19 at 05:16

3

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

answered Jul 31 '19 at 05:16

Syed Mohammad Hosseini

474
1
6
15

So you marked your own response as a solution even when it's the same as in your quesdtion? – alete Dec 15 '20 at 21:27
1

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! @alete – Syed Mohammad Hosseini Dec 21 '20 at 08:14

score 1 · Answer 2 · answered Jul 30 '19 at 14:59

1

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>

answered Jul 30 '19 at 14:59

Y.K.

682
4
10

Thanks but my query is more efficient than yours. I mean I wanted to prevent 2 extra math function not to add extra functions – Syed Mohammad Hosseini Jul 31 '19 at 07:21
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) – Y.K. Jul 31 '19 at 08:50

score 0 · Answer 3 · answered Jul 30 '19 at 18:50

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

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

3 Answers3