Re: Setting week starting day

Bruno Wolff III <bruno@wolff.to> writes:

> On Fri, Mar 09, 2007 at 20:13:11 -0300,
>   Jorge Godoy <jgodoy@gmail.com> wrote:
>> Bruno Wolff III <bruno@wolff.to> writes:
>>
>> > No, it has to be inside the function so that the modular arithmetic is
>> > applied to it.
>>
>> Then there's the error I've shown from your command.  Can you give me a
>> working one?  This was with PostgreSQL 8.2.3.
>
> postgres=# select date_trunc('week', current_date + 1);
>        date_trunc
> ------------------------
>  2007-03-05 00:00:00-06
> (1 row)
>
> It turns out DOW isn't available for date_trunc. You can probably use
> extract to get what you want. You probably should check that it works
> at DST transitions, since the date value is cast to a timestamp and
> if DST transitions happen at 0000 in your time zone, you might get an
> unexpected answer.
>
> postgres=# select extract(dow from current_date + 1);
>  date_part
> -----------
>          6
> (1 row)

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.

--
Jorge Godoy      <jgodoy@gmail.com>