Week numbers and calculating weekly statistics/diagrams

On Tue, Feb 16, 2010 at 01:14:26PM +0100, Alexander Farber wrote:

Does anybody has an advice how to save the week number?

If I save it as a timestamp then calculating realtime statistics
(on a player profile click) will probably be CPU-intensive,
because I have to calculate the week numbers each time.

You should probably seperate the storage from the representation. The
easiest way of storing the information of a week is the date of the
first day (after all, a week could begin on a sunday or monday,
depending on your point of view). This will make grouping and searching
quick, as it's just an integer.

If you really wanted to you could choose an epoch and count weeks from
there but I doubt that's worth the effort.

As for how you represent it to the users, you'll have to create some
conversion routine for output, but I seriously doubt that's going to be
a bottleneck.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Alexander Farber wrote:

Does anybody has an advice how to save the week number?

If I save it as a timestamp then calculating realtime statistics
(on a player profile click) will probably be CPU-intensive,
because I have to calculate the week numbers each time.

If I save it as string "2010/52" then it's difficult to show
statistics for a period of time (like for the last 12 months)
if there is a new year inbetween.

You can save it as a timestamp computed by rounding to a week resolution:

$ psql -x -c "select
current_timestamp,date_trunc('week',current_timestamp);"
-[ RECORD 1 ]----------------------------
now | 2010-02-16 08:21:12.93011-05
date_trunc | 2010-02-15 00:00:00-05

The idea of a "week number" doesn't make any sense really, just
introduces lots of roll-over issues to even try and compute one. If you
think of and display this week as "the week beginning on 2010-02-15"
instead, lots of these problems go away. The only tricky part is
dealing with the classic UPSERT issue, that the first update of the week
is going to actually be an INSERT instead.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us

On 16 February 2010 12:14, Alexander Farber <alexander.farber@gmail.com> wrote:

Hello,

I have multiplayer card game in Flash/Perl/C and would
like to add weekly tournaments/player ratings to it.

This means I have to add a table which holds:
player id, weekly score (which I update after each round)
and the week number.

Does anybody has an advice how to save the week number?

If I save it as a timestamp then calculating realtime statistics
(on a player profile click) will probably be CPU-intensive,
because I have to calculate the week numbers each time.

If I save it as string "2010/52" then it's difficult to show
statistics for a period of time (like for the last 12 months)
if there is a new year inbetween.

Maybe there is a better way?

I'm using postgresql-server-8.3.6 and OpenBSD 4.5

Regards
Alex

If you're worried about CPU overhead, couldn't you just index using an
expression?

Such as:

CREATE INDEX this_index ON results (extract(week from game_date));

Or even a multicolumn index like:

CREATE INDEX this_index ON results (extract(week from game_date), player_id);

Then:

SELECT extract(week from game_date), player_id, sum(score)
FROM results
GROUP BY extract(week from game_date), player_id
ORDER BY extract(week from game_date), player_id

Thom