MySQL：如何选择本周的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20120693/
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
MySQL: How to select records for this week?
提问by juergen d
I have table tempwith structure on sqlfiddle:
我temp在sqlfiddle上有结构表：
id(int 11 primary key)
name(varchar 100)
name2(varchar 100)
date(datetime)
I would like get record on this week, example if now 21.11.2013 i would like all rows on 18.11.2013 to 24.11.2013(on week)
我想获得本周的记录，例如，如果现在 21.11.2013 我想要 18.11.2013 到 24.11.2013(on week) 的所有行
Now I see next algorithm:
现在我看到下一个算法：
- obtain weekday
- calculate how many days ago was Monday
- calculate the date Monday
- calculate future date Sunday
- make a request on date
- 获得工作日
- 计算几天前是星期一
- 计算星期一的日期
- 计算未来日期星期日
- 在日期提出请求
Tell me please, is exist a shorter algorithm (preferably in the query MySQL)?
请告诉我，是否存在更短的算法（最好在查询 MySQL 中）？
ADD Question is:Why this queryselect record on date 17.11.2013(Sunday) - 23.11.2013(Saturday)and how get records on date 18.11.2013(Monday) - 24.11.2013(Sunday)?
ADD 问题是：为什么这个查询选择日期记录17.11.2013(Sunday) - 23.11.2013(Saturday)以及如何获取日期记录18.11.2013(Monday) - 24.11.2013(Sunday)？
query:
询问：
select * from temp
where yearweek(`date`) = yearweek(curdate())
Thanks!
谢谢！
回答by juergen d
Use YEARWEEK():
使用YEARWEEK()：
SELECT *
FROM your_table
WHERE YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1)
回答by Ramesh
Use YEARWEEK. If you use WEEKOFYEARyou will get records of previous years also.
使用YEARWEEK. 如果您使用，WEEKOFYEAR您还将获得前几年的记录。
SELECT id, name, date
FROM table
WHERE YEARWEEK(date)=YEARWEEK(NOW());
回答by ganji
For selecting records of day, week and month use this way:
要选择日、周和月的记录，请使用以下方式：
function my_func($time, $your_date) {
if ($time == 'today') {
$timeSQL = ' Date($your_date)= CURDATE()';
}
if ($time == 'week') {
$timeSQL = ' YEARWEEK($your_date)= YEARWEEK(CURDATE())';
}
if ($time == 'month') {
$timeSQL = ' Year($your_date)=Year(CURDATE()) AND Month(`your_date`)= Month(CURDATE())';
}
$Sql = "SELECT * FROM your_table WHERE ".$timeSQL
return $Result = $this->db->query($Sql)->result_array();
}
回答by dipenparmar12
You can do it by following method
您可以通过以下方法进行
SELECT DATE_FORMAT("2017-06-15", "%U");
Get number of week (From 00 to 53 ) Where (Sunday 1st day and Monday last day of week)
获取周数（从 00 到 53 ）哪里（星期日第一天和星期一最后一天）
May be useful for you.
可能对你有用。
example:
例子：
SELECT DISTINCT DATE_FORMAT('dates', '%U') AS weekdays FROM table_name;
