sorting by week? - Mailing list pgsql-general

From Phil Glatz
Subject sorting by week?
Date
Msg-id 5.1.0.14.2.20020730105850.03af6e90@flawless.net
Whole thread Raw
Responses Re: sorting by week?  (Martijn van Oosterhout <kleptog@svana.org>)
List pgsql-general
What's the best way to group items for weekly summaries? I can group by the
week of the month, or the week of the year -- suppose I wanted to make a
report for each week of each month, with most months ending with a partial
week - is this commonly done, or is the week of the year the most common
format?

I'm currently grouping by aliases like

TO_CHAR(date_field,'WW MM/DD/YY') AS fday

and stripping out the week and date parts when I display them



I also tried doing a by day grouping to check the values returned by the
'W' and 'WW' formats, with TO_CHAR(lt_modified,'MM/DD/YY W WW') AS fday

  05/31/02 4 21 |   5
  06/01/02 0 21 |   6
  06/02/02 1 22 |   3

Why is there a zero for the month week on 6/1?


What I'd really like is an easy way to display counts of rows in each week
of the month, and be able to easily indicate the starting day of the week,
i.e.

Week      | Count
06/01/02  |   147
06/08/02  |   118
06/15/02  |   161
06/23/02  |   138
06/29/02  |    27

Can this be done in pure SQL?  I'm using 7.0.3
thanks!



pgsql-general by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: [ADMIN] log sql?
Next
From: Andrew Sullivan
Date:
Subject: Re: PosgreSQL 10 things to know about ?