I think you can coax the date_trunc function to give you a proper start
day. I think it's more than adding an integer to your date, though.
You also have to do some mod work after the function returns, I think.
I agree that the point isn't that you can't do it with some effort,
however. It's mainly that it's a bit linguistically unintuitive. It
would be nice to have a start date as an argument to the function.
Having said that, my own personal use of it will definitely be inside
another "wrapper" function because I need database platform
independence, so I need to abstract the function to look the same on all
of my platforms.
Jorge Godoy wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
>
>> On Thu, Mar 08, 2007 at 20:32:22 -0300,
>> Jorge Godoy <jgodoy@gmail.com> wrote:
>>
>>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>>
>>> As I said, it is easy with a function. :-) I was just curious to see if we
>>> had something like Oracle's NEXT_DAY function or something like what I
>>> described (SET BOW=4; -- makes Thursday the first day of week):
>>>
>> If you are actually using "date" you can get the effect you want by adding
>> a constant integer to the date in the date_trunc function. That seems
>> pretty easy.
>>
>
>
> I couldn't see where to specify that integer. Or, if it to sum it up to the
> date, something that calculates it automatically.
>
> http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
>
> Adding an integer I'd still have to write the verifications (such as the one I
> mention below for Oracle's NEXT_DATE()) to get the desired result.
>
>
> 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.
>
>
> Oracle's NEXT_DAY() gets closer to that, but would still require a few
> operations (checking if the returned date is before the given date or if after
> then subtract one week from this returned value, kind of a
> "PREVIOUS_DATE()"...).
>
>
> With a function I could make it easily, but then I'd have to wrap all
> calculations with that... It was just something to make life easier. From
> the answers I'm getting I see that there's no way to do that without a
> function and that I'm not missing any feature on PG with regards to that ;-)
>
>
