Re: Setting week starting day

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:

> Jorge Godoy escribió:
>
>> Just to repeat my question:
>>
>> (I don't want to write a function, I can do that pretty easily... And I was
>> asking if there existed some feature on the database that... It's just a
>> curiosity)
>>
>> Given a date X it would return me the first day of the week so that I can
>> make this first day an arbitrary day, e.g. Friday or Wednesday.
>
> When you say "it would return", what's the "it"?

The function that came with the database, the feature, the something. :-)

> I wasn't proposing to use any function, just putting a simple expression
> in the SELECT's result list (and maybe the GROUP BY, etc).

So I'm blind on how to do that. Maybe some "CASE"?

Here's what I was asking for (Sunday=0, Saturday=6, to remember ;-)):

================================================================================
testdb=# select current_date;
date
------------
2007-03-09
(1 row)

testdb=# select current_date + '3 weeks'::interval;
?column?
---------------------
2007-03-30 00:00:00
(1 row)

testdb=# select date_trunc('week', current_date + '3 weeks'::interval);
date_trunc
---------------------
2007-03-26 00:00:00
(1 row)

testdb=# select date_part('dow', date_trunc('week', current_date + '3 weeks'::interval));
date_part
-----------
1
(1 row)

testdb=#
================================================================================

This is the standard behavior. It returns me the first monday. Now, if I had
the week starting on Wednesdays, I should get 2007-03-28 instead of
2007-03-26.

I can check in a function to see if the returned date is before or after my
desired week-start-day (as in Wednesdays, for example) and if date_part('dow',
date) is bigger than it return the value for Monday + 2 days, if it is lower
then return Monday - 5 days.

For example, again:

================================================================================
testdb=# select date_part('dow', current_date + '3 weeks'::interval);
date_part
-----------
5
(1 row)

testdb=# select date_trunc('week', current_date + '3 weeks'::interval) + '2 days'::interval;
?column?
---------------------
2007-03-28 00:00:00
(1 row)

testdb=#
================================================================================

That would be the "first day" of the week in three weeks from now, with weeks
starting on Wednesdays.

If I had asked for this 3 days ago:

================================================================================
testdb=# select date_trunc('week', current_date - '3 days'::interval + '3 weeks'::interval);
date_trunc
---------------------
2007-03-26 00:00:00
(1 row)

testdb=# select date_part('dow', current_date - '3 days'::interval + '3 weeks'::interval);
date_part
-----------
3
(1 row)

testdb=# select date_trunc('week', current_date - '3 days'::interval + '3 weeks'::interval) - '5 days'::interval;
?column?
---------------------
2007-03-21 00:00:00
(1 row)

testdb=#
================================================================================

Then if it was Tuesday, the week three weeks from now would have started on
Wednesday, 2007-03-21.

It is not hard to calculate, as you can see... but it would be nice if
"date_trunc('week', date)" could do that directly. Even if it became
"date_trunc('week', date, 4)" or "date_trunc('week', date, 'Wednesday')" it
would be nice... :-) And that is what I was trying to ask ;-)

Thanks for your attention, Alvaro. :-)

--
Jorge Godoy <jgodoy(at)gmail(dot)com>