MySQL 如何在mysql中获取日期的一周的第一天？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6944055/
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 get the first day of the week of a date in mysql?
提问by davidahines
Suppose I have 2011-01-03 and I want to get the first of the week, which is sunday, which is 2011-01-02, how do I go about doing that?
假设我有 2011-01-03 并且我想获得一周的第一个，也就是星期日，也就是 2011-01-02，我该怎么做？
The reason is I have this query:
原因是我有这个查询：
select
YEAR(date_entered) as year,
date(date_entered) as week, <-------This is what I want to change to select the first day of the week.
SUM(1) as total_ncrs,
SUM(case when orgin = picked_up_at then 1 else 0 end) as ncrs_caught_at_station
from sugarcrm2.ncr_ncr
where
sugarcrm2.ncr_ncr.date_entered > date('2011-01-01')
and orgin in(
'Silkscreen',
'Brake',
'Assembly',
'Welding',
'Machining',
'2000W Laser',
'Paint Booth 1',
'Paint Prep',
'Packaging',
'PEM',
'Deburr',
'Laser ',
'Paint Booth 2',
'Toolpath'
)
and date_entered is not null
and orgin is not null
AND(grading = 'Minor' or grading = 'Major')
and week(date_entered) > week(current_timestamp) -20
group by year, week(date_entered)
order by year asc, week asc
And yes, I realize that origin is spelled wrong but it was here before I was so I can't correct it as too many internal apps reference it.
是的，我意识到 origin 拼写错误，但它在我之前就在这里，所以我无法更正它，因为有太多内部应用程序引用它。
So, I am grouping by weeks but I want this to populate my chart, so I can't have all the beginning of weeks looking like different dates. How do I fix this?
所以，我按周分组，但我希望它填充我的图表，所以我不能让几周的所有开始看起来都像不同的日期。我该如何解决？
回答by Грозный
回答by Stéphane
If you need to handle weeks which start on Mondays, you could also do it that way. First define a custom FIRST_DAY_OF_WEEKfunction:
如果您需要处理从星期一开始的几周，您也可以这样做。首先定义一个自定义FIRST_DAY_OF_WEEK函数：
DELIMITER ;;
CREATE FUNCTION FIRST_DAY_OF_WEEK(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
RETURN SUBDATE(day, WEEKDAY(day));
END;;
DELIMITER ;
And then you could do:
然后你可以这样做：
SELECT FIRST_DAY_OF_WEEK('2011-01-03');
For your information, MySQL provides two different functions to retrieve the first day of a week. There is DAYOFWEEK:
供您参考，MySQL 提供了两种不同的函数来检索一周的第一天。有DAYOFWEEK：
Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.
返回日期的工作日索引（1 = 星期日，2 = 星期一，……，7 = 星期六）。这些索引值对应于 ODBC 标准。
And WEEKDAY:
和工作日：
Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).
返回日期的工作日索引（0 = 星期一，1 = 星期二，... 6 = 星期日）。
回答by Egene
If week starts on Monday
如果一周从星期一开始
SELECT SUBDATE(mydate, weekday(mydate));
If week starts on Sunday
如果一周从星期日开始
SELECT SUBDATE(mydate, dayofweek(mydate) - 1);
Example:
例子：
SELECT SUBDATE('2018-04-11', weekday('2018-04-11'));
2018-04-09
2018-04-09
SELECT SUBDATE('2018-04-11', dayofweek('2018-04-11') - 1);
2018-04-08
2018-04-08
回答by Cem Güler
select '2011-01-03' - INTERVAL (WEEKDAY('2011-01-03')+1) DAY;
选择 '2011-01-03' - INTERVAL (WEEKDAY('2011-01-03')+1) 天；
returns the date of the first day of week. You may look into it.
返回一周的第一天的日期。你可以研究一下。
回答by Ghanshyam Nakiya
Week starts day from sunday then get First date of the Week and Last date of week
一周从星期日开始，然后获取一周的第一个日期和一周的最后一个日期
SELECT
DATE("2019-03-31" + INTERVAL (1 - DAYOFWEEK("2019-03-31")) DAY) as start_date,
DATE("2019-03-31" + INTERVAL (7 - DAYOFWEEK("2019-03-31")) DAY) as end_date
Week starts day from Monday then get First date of the Week and Last date of week
一周从星期一开始，然后获取一周的第一个日期和一周的最后一个日期
SELECT
DATE("2019-03-31" + INTERVAL ( - WEEKDAY("2019-03-31")) DAY) as start_date,
DATE("2019-03-31" + INTERVAL (6 - WEEKDAY("2019-03-31")) DAY) as end_date
回答by Kenny_D
This is a much simpler approach than writing a function to determine the first day of a week.
这比编写一个函数来确定一周的第一天要简单得多。
Some variants would be such as SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1)DAY),INTERVAL 7 DAY)(for the ending date of a query, such as between "beginning date" and "ending date"). SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY(for the beginning date of a query).
一些变体将例如SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1)DAY),INTERVAL 7 DAY)（对于查询的结束日期，例如在“开始日期”和“结束日期”之间）。SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY（对于查询的开始日期）。
This will return all values for the current week. An example query would be as follows:SELECT b.foo FROM bar bWHERE b.datefield BETWEEN(SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY)AND(SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1)DAY),INTERVAL 7 DAY))
这将返回当前周的所有值。示例查询如下：SELECT b.foo FROM bar bWHERE b.datefield BETWEEN(SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY)AND(SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1)DAY),INTERVAL 7 DAY))
回答by Paul Ishak
This works form me
这对我有用
Just make sure both dates in the below query are the same...
只需确保以下查询中的两个日期相同...
SELECT ('2017-10-07' - INTERVAL WEEKDAY('2017-10-07') Day) As `mondaythisweek`
This query returns: 2017-10-02 which is a monday,
此查询返回： 2017-10-02 这是一个星期一，
But if your first day is sunday, then just subtract a day from the result of this and wallah!
但是如果你的第一天是星期天，那么只需从这个和 wallah 的结果中减去一天！
回答by Павел П
If the week starts on Monday do this:
如果一周从星期一开始，请执行以下操作：
DATE_SUB(mydate, INTERVAL WEEKDAY(mydate) DAY)
