sorting by week?

From: Phil Glatz <phil(at)glatz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: sorting by week?
Date: 2002-07-30 18:13:42
Message-ID: 5.1.0.14.2.20020730105850.03af6e90@flawless.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2002-07-30 18:22:47 Re: PosgreSQL 10 things to know about ?
Previous Message Andrew Sullivan 2002-07-30 18:09:39 Re: [GENERAL] log sql?