--- Situation Description ---
I'm writing a quota system to a web-by-mail robot, on a database
which consists ofemail 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...