Re: Setting week starting day

Omar Eljumaily <omar2@omnicode.com> writes:

> 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);

Indeed.  This gives the correct result.  So, we can change '5' for:

     7 - ('dow desired' - 1)

Replacing the above queries, then:

# select (date_trunc('week', '2007-03-07'::date + (7 - (3 - 1))))::date - (7 - (3 - 1));
  ?column?
------------
 2007-03-07
(1 row)

# select (date_trunc('week', '2007-03-06'::date + (7 - (3 - 1))))::date - (7 - (3 - 1));
  ?column?
------------
 2007-02-28
(1 row)

# select (date_trunc('week', '2007-03-08'::date + (7 - (3 - 1))))::date - (7 - (3 - 1));
  ?column?
------------
 2007-03-07
(1 row)



Parameterizing the desired day shouldn't be hard. ;-)


We subtract one from the desired day because PostgreSQL returns '1' for the
date_part('week') considering Mondays as the first day of the week.



Thanks, Omar.  This makes the function easier to write.  I hope it also solves
your problem.



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