Re: group by week (ww), plus -S performance

On Sun, 28 May 2000, Michael Blakeley wrote:

> At 12:54 AM -0400 5/29/2000, Tom Lane wrote:
> >Michael Blakeley <mike(at)blakeley(dot)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;
>
> Thanks - that seems to be about 10% faster. I also got a 25% boost by
> recompiling PG7 with -O3.

Well, support for 'WW' in 'to_date()' is just in my TODO.

Probably will more faster

select to_date( to_char(now(), 'YYYY-WW'), 'YYYY-WW');

than 3 operators and 2 functions in one query.

Or best way will add support for 'start-date' per week / month / quarter to
'to_char()'. Hmm, I try it. But how format-pictires use for this?

SWW / SMM / SQ ?

IMHO, the postgreSQL will first DB with this feature :-)

Karel