On 16 February 2010 12:14, Alexander Farber <alexander.farber@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
