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
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to