Date operations - Mailing list pgsql-hackers

From Oliver Smith
Subject Date operations
Date
Msg-id 19990331115740.D17195@kfs.org
Whole thread Raw
Responses Re: [HACKERS] Date operations
List pgsql-hackers
--- 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...


pgsql-hackers by date:

Previous
From: Oliver Smith
Date:
Subject: Date operations
Next
From: Oliver Smith
Date:
Subject: Re: [HACKERS] Date operations