Re: sorting by week?
От | Martijn van Oosterhout |
---|---|
Тема | Re: sorting by week? |
Дата | |
Msg-id | 20020731102637.B8843@svana.org обсуждение исходный текст |
Ответ на | sorting by week? (Phil Glatz <phil@glatz.com>) |
Список | pgsql-general |
On Tue, Jul 30, 2002 at 11:13:42AM -0700, Phil Glatz wrote: > 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? [snip] > 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 The way I usually acheive this is by saying sometihng like: SELECT datefield - date_part('dow', datefield) as week, count(*) FROM table GROUP BY week; Make sure you're using date fields, not datetime as this trick can do strange things around the daylight savings transitions. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
В списке pgsql-general по дате отправления: