Setting week starting day (was: Re: Tabulate data incrementally)

Richard Huxton <dev(at)archonet(dot)com> writes:

> Omar Eljumaily wrote:
>> I want to tabulate time data on a weekly basis, but my data is entered on a
>> daily basis.
>>
>> create table time_data
>> {
>> employee varchar(10),
>> _date date,
>> job varchar(10),
>> amount
>> }
>>
>> So I want to tabulate with a single sql command. Is that possible?
>
> Try one of these:
>
> => SELECT date_trunc('week',now());
> date_trunc
> ------------------------
> 2007-03-05 00:00:00+00
>
> => SELECT extract(week from now());
> date_part
> -----------
> 10

Hi!

I'm hijacking this thread a bit... Is it possible to specify dinamically the
day of the week when week starts?

I mean, if I wanted to do the above but instead of Sunday or Monday as the
starting day I'd like using Fridays or Wednesdays...

Is it possible? Writing a new function shouldn't be too hard -- it's a matter
of truncating the week on a day and shifting the date forward or backward --,
but something like a "SET bow=5" (to make the API consistent with the 'dow'
that already exists) would be really great!

Why doing that? Imagine an accounting office where all their activities
should be closed and values summed up every Wednesday. Or a company that
tracks the end of their activies weekly and consider the end of the week on
Thursdays (so that they can send invoices on Friday).

Being able to count "the first day of the 'week' 5 weeks from now" for the
above situations would make things easier to code. :-)

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