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 reads into CPU% and the owner of the work, but I am having trouble including only the amount above if it says 14 days (2 weeks) between the current date.
The table sge_job_usage
contains both:
ju_start_time
, ju_end_time
Can anyone help me on how I can check it if it is between the current date - 14 days before then and then checks the date end_time
to see if it keeps doing this?
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;
source to share
ADC syntax is not valid for PostgreSQL.
I don't understand what condition you want to apply to ju_end_date. But it should be similar to the one at 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 a test environment, so I cannot test it, but I think it should work.
For more information on topic validation
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
Edit: as ypercube pointed out, you need a join condition for two tables.
source to share
I am assuming the two columns are of type DATE
. 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 exact condition you want to check:
WHERE ( ju_start_time, ju_end_time )
OVERLAPS
( CURRENT_DATE - INTERVAL '14 days', CURRENT_DATE )
The query uses 2 tables and there are no conditions 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;
source to share