Re: Something like 'to_days' in postgresql? Help with a MySQL migration...

On Thu, Dec 11, 2003 at 01:44:31 -0700,
"Alan T. Miller" <amiller(at)hollywood101(dot)com> wrote:
> If someone thinks this is easy enough, it would be even more helpful if
> someone could suggest the most efficient was to do the same query but
> perhaps return the total for the last 7 days, the last 30 days, and the last
> 90 days in the same query. I know I can run the query three times but I was
> hoping for a suggestion that might be more efficient.

If you want to get several date periods covered in one query, you can
use the CASE statement to get a value of 1 when the record is in the
correct range and 0 when it is not. Doing a SUM of each of three different
CASE statements will get you the numbers you need. You will still want
to use a where clause if the largest period contains only a small fraction
(maybe 10%) of the data so that an index scan can be used instead of
a sequential scan over all of the data.