当前日期和上周之间的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9599027/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query between current date and previous week
提问by Zenaphor
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.
这是我当前的 sql 查询，它只是简单地读取 CPU% 和作业所有者，但是如果它在当前日期之间（例如 14 天（2 周）前），我无法仅合并上面的总和。
In the sge_job_usagetable belongs both:
在sge_job_usage表中属于：
ju_start_time, ju_end_time
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_timedate to see if it was still going then?
任何人都可以帮助我如何让它检查它是否在当前日期 - 14 天之前，然后它检查end_time日期以查看它是否仍在继续？
Answer that Outputs What I need below:
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;
回答by bruno
ADC syntax is not valid for PostgreSQL.
ADC 语法对 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.
我不明白您希望将哪个条件应用于 ju_end_date。但它应该类似于 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
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.
编辑：正如 ypercube 所说，您需要两个表上的连接条件。
回答by ypercube??
I assume the two columns are of the DATEtype. Then you need something like this:
我假设这两列是这种DATE类型。那么你需要这样的东西：
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:
该查询虽然使用了 2 个表，但没有连接条件。它应该是：
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;
回答by Alberto De Caro
It's not clear if you need the same condition for the ju_end_date, but the structure should be:
目前还不清楚 ju_end_date 是否需要相同的条件，但结构应该是：
...
WHERE ju_start_time BETWEEN getdate() and DateAdd(d,-14, getdate())
