Re: Setting week starting day

On Sat, Mar 10, 2007 at 00:03:04 -0300,
Jorge Godoy <jgodoy(at)gmail(dot)com> wrote:
>
> 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.)

If for some reason you actually need to display the date of the first day
of the week, rather than just group by it, then subtract the number of
days that were added inside, on the outside. Because date_trunc returns
a timestamp with timezone, you need to subtract an interval (or cast
back to date and subtract an integer). If you are getting the '5' from
somewhere hard coded you might want to use (5 * '1 day'::interval) rather
than '5 days'::interval .

So you would use:
select date_trunc('week', '2007-03-08'::date + 5) - '5 days'::interval;

postgres=# select date_trunc('week', '2007-03-08'::date + 5) - '5 days'::interval;
?column?
------------------------
2007-03-07 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-07'::date + 5) - '5 days'::interval;
?column?
------------------------
2007-03-07 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-06'::date + 5) - '5 days'::interval;
?column?
------------------------
2007-02-28 00:00:00-06
(1 row)