Re: Week numbers and calculating weekly statistics/diagrams

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