Re: Setting week starting day - Mailing list pgsql-general

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

> On Fri, Mar 09, 2007 at 23:07:26 -0300,
>   Jorge Godoy <jgodoy@gmail.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.

I believe you either missed my post with several queries showing what I wanted
or you didn't understand the point.

If I run this query:

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

it fails even for that date.  The correct answer, would be 2007-03-07 and not
2007-03-12.  I want the first day of the week to be Wednesday and hence I want
the Wednesday for the week the date is in.  (Wednesday was arbitrarily chosen,
it could be Thursday, Tuesday, Friday, etc.)


> postgres=# select date_trunc('week', '2007-03-07'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-12 00:00:00-05
> (1 row)

This should be 2007-03-07 since 2007-03-07 *is* a Wednesday and that's when
the week starts.

> postgres=# select date_trunc('week', '2007-03-06'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-05 00:00:00-06
> (1 row)

This should be 2007-02-28 since this is the first day of the week for the week
that starts on Wednesday 2007-02-28 and ends on 2007-03-06.

> postgres=# select date_trunc('week', '2007-03-08'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-12 00:00:00-05
> (1 row)

This should return the same date as the first query (2007-03-07).  2007-03-12
is a Monday, and weeks should always start on Wednesday on my arbitrary
question.


This is why I can't envision a simple query for that but it is easy with a
function.

Again, the function should do something like:

       - make the date calculation (e.g. add some interval or nothing at all...)

       - get the resulting 'dow'

       - if it is > than the arbitrary day that was determined to be the first
         day of the week (Wednesday on my example), then return
         date_trunc('week') + 2 days (2 for moving from Monday to Wednesday,
         for different first days the shift should be different)

       - if it is < than the arbitrary day that was determined to be the first
         day of the week (Wednesday, again), then return date_trunc('week') -
         5 days (-5 for moving from Monday to the previous Wednesday)


The result when asked for the first day should always be the Wednesday that is
equal to the date or that ocurred right before it.  It is the same idea that
is implemented today that returns Monday, but instead of Monday I want another
day that in my posts happened to be exemplified by Wednesday.



I don't want you to expend your time.  It was just a question that got
answered indirectly with a "there's no way to do that without using a
function" due to the complexity above and the lack of such feature in
PostgreSQL.  It is simple to have it as a function, though.

I don't know any RDBMS that implements that.  All of them require some
operations to get the desired result.



Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>