Обсуждение: 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