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>
