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

问题描述

假设我有2011-01-03，我想得到星期一的第一个星期日，这是2011-01-02，我该怎么做？

原因是我有这个查询：

  select year 
 YEAR（date_entered）as year ，
 date（date_entered）as week，< -------这是我想要更改以选择一周的第一天。 
 SUM（1）作为total_ncrs，
 SUM（当orgin = pick_up_at然后1 else 0结束时的情况）为ncrs_caught_at_station 
 from sugarcrm2.ncr_ncr 
其中
 sugarcrm2.ncr_ncr .date_entered>日期（'2011-01-01'）
和（
'Silkscreen'，
'Brake'，
'Assembly'，
'Welding' 
'加工'，
'2000W Laser'，
'Paint Booth 1'，
'Paint Prep'，
'Packaging'，
'PEM '，
'Deburr'，
'Laser'，
'Paint Booth 2'，
'Toolpath'
）
 and date_entered is not null 
和orgin不为空
 AND（grading ='Minor'或grading ='Major'）
和week（date_entered）>周（current_timestamp）-20 
组按年，周（date_entered）
按年份排序，周升
  

是的，我意识到原点是拼写错误的，但是在这之前，我是无法纠正的，因为内部应用程序太多引用了。

所以，我按星期分组，但是我想让它填充我的图表，所以我不能让所有的星期几开始看起来像不同的日期。如何解决这个问题？

解决方案

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

  DELIMITER ;; 
 CREATE FUNCTION FIRST_DAY_OF_WEEK（DATE）
返回日期确定
 BEGIN 
 RETURN SUBDATE（day，WEEKDAY（day））; 
 END ;; 
 DELIMITER; 
  

然后你可以这样做：

  SELECT FIRST_DAY_OF_WEEK（'2011-01-03'）; 
  

对于您的信息，MySQL提供了两个不同的功能来检索一周的第一天。 DAYOFWEEK ：

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

和 WEEKDAY ：

返回日期的工作日索引（ 0 =星期一，1 =星期二，6 =星期日）。

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?

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.

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?

解决方案

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 ;

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:

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

And WEEKDAY:

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

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