--- Situation Description ---

I'm writing a quota system to a web-by-mail robot, on a database
which consists of
 email   text,
 url     text,
 action  varchar(8),	-- in "sent", "toobig", "refused", ...
 bytes   int4,
 at      <undeterimined>

What I'm wanting to say is:

 SELECT COUNT(bytes) AS requests, SUM(bytes) as datasent
  FROM  tbl_robot_log
  WHERE email = '$email' AND action = 'sent'
    AND { it was less than an hour ago }

That'll give me the user's usage statistics for the last hour;
requests -> number of requests processed, datasent -> bytes they
have received.

Then I do another request to get their 24 hour stats.

--- Problem ---

Assuming 'at' is a datetime (it's currently an int4 with a unix timestamp in it - yeuch :)

Question 1:

 SELECT COUNT(bytes), SUM(bytes)
   FROM tbl_robot_log
  WHERE email = '$email' AND action = 'sent'
    AND at >= datetime('now' + reltime('-60 mins'::timespan));

When is the datetime(...) expression evaluated?

Is it evaluated per line of data that matches the previous two
expressions? Or is it evaluated once? If so, then it is probably
as efficient as my current operations.


Question 2:

What would be the most efficient way to get the combination of
1 and 24 hour logs? Should I get all entries within the last 24
hours and use a 'group by' statement? If so; how would I do the
group by? Is there a way to say :

 GROUP BY age(at, 'hours')?

Oliver
-- 
If at first you don't succeed, skydiving is not for you...

Wed, 31 Mar 1999 11:54:02 +0100
Oliver Smith <oliver@kfs.org>
Date operations