Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxx>
