Re: Setting week starting day

But you're always returning Monday, right? Your grouping will be
correct, but to get the actual truncation date, you have to subtract back.

select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
select (date_trunc('week', '2007-03-08'::date + 5)::date-5);

Bruno Wolff III wrote:
> On Fri, Mar 09, 2007 at 23:07:26 -0300,
> Jorge Godoy <jgodoy(at)gmail(dot)com> wrote:
>
>> But how to get the date if the first day of the week is a Wednesday? This
>> example is like the ones I've sent with separate queries that needed being
>> combined -- in a function, probably -- to get the desired result.
>>
>
> If you want to group on weeks that start on Wednesdays add 5.
>
> postgres=# select date_trunc('week', '2007-03-07'::date + 5);
> date_trunc
> ------------------------
> 2007-03-12 00:00:00-05
> (1 row)
>
> postgres=# select date_trunc('week', '2007-03-06'::date + 5);
> date_trunc
> ------------------------
> 2007-03-05 00:00:00-06
> (1 row)
>
> postgres=# select date_trunc('week', '2007-03-08'::date + 5);
> date_trunc
> ------------------------
> 2007-03-12 00:00:00-05
> (1 row)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>