Thread: Date operations

--- 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...

On Wed, Mar 31, 1999 at 11:57:40AM +0100, Oliver Smith wrote:
> Question 2:

Oh - and 

Question 3:

Because this query gets hit a lot, would it make sense to create an
index
CREATE INDEX tbl_robot_logs_at_age_idx ON tbl_robot_logs ( AGE(at) );

If so - how should I word the query to use this?
SELECT * FROM tbl_robot_logs WHERE email = '$email' AND action = 'sent'   AND AGE(at) > '-1 hour'::timespan;

Will the query optimizer recognise that it has an index to suit this?


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

> 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?

The function calls are probably evaluated once per query. And you can
simplify your query a bit:
 AND at >= ('now'::datetime + '-60 mins'::timespan);

> 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')?

The function you want is date_part('hours', at). Not sure if the GROUP
BY would be happy with it, but istm that you might be able to select a
column of date_part() and then group on that...
                  - Tom

> Question 3:
> Because this query gets hit a lot, would it make sense to create an
> index
>  CREATE INDEX tbl_robot_logs_at_age_idx ON tbl_robot_logs
>   ( AGE(at) );

Nope, since "age" is calculated from "now", which is alway changing.
You can't make a useful index on a non-constant expression or
function.
                       - Tom