Michael Blakeley <mike@blakeley.com> writes:
> SELECT to_date(date_part('year',stamp),'yyyy')-5+7*date_part('week',stamp)
> as week,count(*) FROM EVENTS GROUP BY week ORDER BY week;
Seems like the hard way. You are relying on an implicit conversion from
the float8 output of date_part() to the text input to_date expects,
which is kind of slow (and IMHO it's a bug in 7.0 that it will do such
a conversion silently, anyhow). Better to use date_trunc to save the
conversion step:
SELECT date_trunc('year',stamp)-5+7*date_part('week',stamp)
as week,count(*) FROM EVENTS GROUP BY week ORDER BY week;
But the real problem is that this is going to use a start-of-week day
that is offset five days from whatever day-of-the-week 1 January is.
If you'd tried a few other years than 2000 you'd likely have been
dissatisfied with the results...
It seems like the right answer is that date_trunc('week',stamp) ought
to do what you want, but it doesn't seem to be implemented. That's
definitely a shortcoming --- anyone want to fix it?
> The order-by clause doesn't seem to add much overhead - the query
> plan is the same with or without it.
Right, the GROUP BY requires a sort anyway, so the planner knows
there's no need to sort again on the same key.
regards, tom lane