Thread: getting weekly total based on daily...

i was wondering is there a built-in funtion that gets the weeknumber based on
date..

lets say Jan 1-7 is week 1
                   8-14 is week 2

then
Feb 1-7 week 1

etc..

TIA

On Mon, Jan 27, 2003 at 16:52:50 +0800,
  jerome <jerome@gmanmi.tv> wrote:
> i was wondering is there a built-in funtion that gets the weeknumber based on
> date..

area=> select extract(week from current_date);
 date_part
-----------
         4
(1 row)

This is the ISO week of the year. Week 1 is the week containing the first
Thursday of the year. Weeks go from Monday to Sunday.

but this gives me week on year not week of month...

:)

On Monday 27 January 2003 11:31, you wrote:
> On Mon, Jan 27, 2003 at 16:52:50 +0800,
>
>   jerome <jerome@gmanmi.tv> wrote:
> > i was wondering is there a built-in funtion that gets the weeknumber
> > based on date..
>
> area=> select extract(week from current_date);
>  date_part
> -----------
>          4
> (1 row)
>
> This is the ISO week of the year. Week 1 is the week containing the first
> Thursday of the year. Weeks go from Monday to Sunday.

On Mon, Jan 27, 2003 at 23:34:02 +0800,
  jerome <jerome@gmanmi.tv> wrote:
> but this gives me week on year not week of month...

If that is what you want then you want something like:
area=> select extract (day from current_date)::int / 7;
 ?column?
----------
        3
(1 row)

However, you will need to decide for yourself how to count the first
partial week. Depending on what you decide, you will need a different
formula to produce.

>
> On Monday 27 January 2003 11:31, you wrote:
> > On Mon, Jan 27, 2003 at 16:52:50 +0800,
> >
> >   jerome <jerome@gmanmi.tv> wrote:
> > > i was wondering is there a built-in funtion that gets the weeknumber
> > > based on date..
> >
> > area=> select extract(week from current_date);
> >  date_part
> > -----------
> >          4
> > (1 row)
> >
> > This is the ISO week of the year. Week 1 is the week containing the first
> > Thursday of the year. Weeks go from Monday to Sunday.
>