Re: Week numbers and calculating weekly statistics/diagrams

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Week numbers and calculating weekly statistics/diagrams
Date: 2010-02-16 13:31:07
Message-ID: 4B7A9E1B.3020808@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)2ndQuadrant(dot)com www.2ndQuadrant.us

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2010-02-16 13:52:43 Re: Week numbers and calculating weekly statistics/diagrams
Previous Message Peter Geoghegan 2010-02-16 13:09:20 Re: Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion