如何在mysql中获取日期的一周的第一天? [英] How do I get the first day of the week of a date in mysql?

问题描述

假设我有 2011-01-03 并且我想获得一周的第一天，也就是星期日，也就是 2011-01-02，我该怎么做?

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?

原因是我有这个查询:

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 

是的，我意识到 origin 拼写错误，但它在我之前就在这里，所以我无法更正它，因为有太多内部应用程序引用它.

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.

所以，我按周分组，但我希望它填充我的图表，所以我不能让所有周的开始看起来都像不同的日期.我该如何解决这个问题?

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?

推荐答案

如果您需要处理从星期一开始的周，您也可以这样做.首先定义一个自定义的FIRST_DAY_OF_WEEK函数:

If you need to handle weeks which start on Mondays, you could also do it that way. First define a custom FIRST_DAY_OF_WEEK function:

DELIMITER ;;
CREATE FUNCTION FIRST_DAY_OF_WEEK(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
  RETURN SUBDATE(day, WEEKDAY(day));
END;;
DELIMITER ;

然后你可以这样做:

SELECT FIRST_DAY_OF_WEEK('2011-01-03');

供您参考，MySQL 提供了两种不同的函数来检索一周的第一天.有 DAYOFWEEK:

For your information, MySQL provides two different functions to retrieve the first day of a week. There is DAYOFWEEK:

返回日期的工作日索引(1 = 星期日，2 = 星期一，……，7 = 星期六).这些索引值对应于 ODBC 标准.

Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.

和 WEEKDAY:

返回日期的工作日索引(0 = 星期一，1 = 星期二，... 6 = 星期日).

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

这篇关于如何在mysql中获取日期的一周的第一天?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！