MySQL 如何在MYSQL中选择两周前？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6683768/
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
How do I select two weeks ago in MYSQL?
提问by davidahines
I have a report that is driven by a sql query that looks like this:
我有一个由 sql 查询驱动的报告，如下所示：
SELECT batch_log.userid,
batches.operation_id,
SUM(TIME_TO_SEC(ramses.batch_log.time_elapsed)),
SUM(ramses.tasks.estimated_nonrecurring + ramses.tasks.estimated_recurring),
DATE(start_time)
FROM batch_log
JOIN batches ON batch_log.batch_id=batches.id
JOIN ramses.tasks ON ramses.batch_log.batch_id=ramses.tasks.batch_id
JOIN protocase.tblusers on ramses.batch_log.userid = protocase.tblusers.userid
WHERE DATE(ramses.batch_log.start_time) > "2011-02-01"
AND ramses.batch_log.time_elapsed > "00:03:00"
AND DATE(ramses.batch_log.start_time) < now()
AND protocase.tblusers.active = 1
AND protocase.tblusers.userid NOT in ("ksnow","smanning", "dstapleton")
GROUP BY userid, batches.operation_id, date(start_time)
ORDER BY start_time, userid ASC
Since this is to be compared with the time from the current payperiod it causes an error.
Our pay periods start on a Sunday, the first pay period was 2011-02-01 and our last pay period started the 4th of this month. How do I put that into my where statement to strip the most recent pay period out of the query?
由于要与当前支付期的时间进行比较，因此会导致错误。
我们的支付期从星期日开始，第一个支付期是 2011-02-01，我们的最后一个支付期从本月 4 日开始。我如何将它放入我的 where 语句中以从查询中删除最近的支付期？
EDIT: So now I'm using date_sub(now(), INTERVAL 2 WEEK) but I really need a particular day of the week(SUNDAY) since it is wednesday it's chopping it off at wednesday.
编辑：所以现在我正在使用 date_sub(now(), INTERVAL 2 WEEK) 但我真的需要一周中的某个特定日期（星期日），因为它是星期三，它在星期三被砍掉。
回答by Reverend Gonzo
You want to use DATE_SUB, and as an example.
Specifically:
具体来说：
select DATE_SUB(curdate(), INTERVAL 2 WEEK)
gets you two weeks ago. Insert the DATE_SUB ... part into your sql and you're good to go.
两周前给你。将 DATE_SUB ... 部分插入到您的 sql 中，您就可以开始了。
Edit per your comment:
根据您的评论编辑：
Check out DAYOFWEEK:
查看DAYOFWEEK：
and you can do something along the lines of:
你可以做一些事情：
DATE_SUB(DATE_SUB(curdate(), INTERVAL 2 WEEK), INTERVAL 2 + DAYOFWEEK(curdate()) DAY)
(I don't have a MySql instance to test it on .. but essentially subtract the number of days after Monday.)
（我没有一个 MySql 实例来测试它......但基本上减去星期一之后的天数。）
回答by ain
Question isn't quite clear, especially after the edit - it isn't clear now is the "pay period" two weeks long or do you want just last two weeks back from last sunday? I assume that the period is two weeks... then you first need to know how many days the latest period (which you want to ignore, as it isn't over yet) has been going on. To get that number of days you can use expression like
问题不太清楚，尤其是在编辑之后 - 现在不清楚“支付期”是两周长还是你想要从上周日开始的最后两周？我假设这个时期是两周......然后你首先需要知道最近的时期（你想忽略它，因为它还没有结束）已经持续了多少天。要获得该天数，您可以使用类似的表达式
DATEDIFF(today, FirstPeriod) % 14
where FirstPeriodis 2011-02-01. And now you strip that number of days from the current date in the query using date_sub(). The exact expression depends on how the period is defined but you should get the idea...
哪里FirstPeriod是2011-02-01。现在您使用date_sub(). 确切的表达取决于周期的定义方式，但您应该明白……
