CODEWITHSUNDEEP
sqlpostgresql
Zenaphor
Zenaphor

Reputation: 801

SQL query between current date and previous week

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;

Upvotes: 9

Views: 30522

Answers (3)

bruno
bruno

Reputation: 2882

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.

Upvotes: 18

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

I assume the two columns are of the DATE type. Then you need something like this:

WHERE ju_start_time BETWEEN CURRENT_DATE - INTERVAL '14 days'
                        AND CURRENT_DATE
  AND ju_end_time >= CURRENT_DATE

or this (depending on what condition exactly you want to check:

WHERE ( ju_start_time, ju_end_time ) 
      OVERLAPS 
      ( CURRENT_DATE - INTERVAL '14 days', CURRENT_DATE )

The query uses 2 tables though and no condition for the Join. It should be:

SELECT sge_job.j_owner AS "Owner"
      ,SUM(sge_job_usage.ju_cpu) AS "CPU Total"
FROM sge_job_usage
  JOIN sge_job
    ON sge_job.SomeColumn = sge_job_usage.SomeColumn 
WHERE   ...
GROUP BY j_owner;

Upvotes: 5

Alberto De Caro
Alberto De Caro

Reputation: 5213

It's not clear if you need the same condition for the ju_end_date, but the structure should be:

...
WHERE ju_start_time BETWEEN getdate() and DateAdd(d,-14, getdate())

Upvotes: 2

Related Questions