Re: Setting week starting day - Mailing list pgsql-general

Alvaro Herrera <alvherre@commandprompt.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@gmail.com>