SQL query between current date and previous week

Question

SELECT sge_job.j_owner AS "Owner"
      ,SUM(sge_job_usage.ju_cpu) AS "CPU Total"
FROM sge_job_usage, sge_job 
GROUP BY j_owner;

Here is my current sql query that just simply reads in CPU% and job owner but I'm having trouble incorporating only sum above if it's between current date say 14days(2 weeks) ago.

In the sge_job_usage table belongs both:

ju_start_time, ju_end_time

Anyone help me towards how I can get it to check if it's between current date - 14 days before and then it checks end_time date to see if it was still going then?

Answer that Outputs What I need below:

SELECT sge_job.j_owner AS "Owner"       
,SUM(sge_job_usage.ju_cpu) AS "CPU Total" 
FROM sge_job_usage, sge_job  
WHERE ju_start_time BETWEEN LOCALTIMESTAMP - INTERVAL '1 week' AND LOCALTIMESTAMP 
GROUP BY j_owner;

Answer 1

ADC syntax is not valid for PostgreSQL.

I don't understand which condition do you want to be applied to ju_end_date. But it should be similar to the one on ju_start_time.

SELECT sge_job.j_owner AS "Owner"
      ,SUM(sge_job_usage.ju_cpu) AS "CPU Total"
FROM sge_job_usage, sge_job 
WHERE sge_job_usage.C1 = sge_job.C2
  AND ju_start_time BETWEEN LOCALTIMESTAMP - INTERVAL '14 days' AND LOCALTIMESTAMP
GROUP BY j_owner;

I don't have an a test environment so I can't test this but I think it should work.

For more information on the subject check

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

Edit: As ypercube as said, you need a join condition on the two tables.