Re: Setting week starting day

Omar Eljumaily <omar2(at)omnicode(dot)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(at)gmail(dot)com>