如何在MySQL中获取一个日期的周的第一天？

Question

假设我有一个日期2011-01-03，我想要获取这一周的第一天，也就是星期天即2011-01-02，我该怎么做？

原因是我有以下查询：

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 的拼写错误，但在我来之前它就已经存在了，所以我不能更正它，因为太多的内部应用程序引用了它。

因此，我按周分组，但我希望这可以填充我的图表，所以我不能让所有开头的周看起来像不同的日期。我该怎么解决这个问题？

- davidahines

Answer 1

如果一周从星期日开始，就这样做：

DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)

如果一周是从星期一开始，则执行以下操作：

DATE_ADD(mydate, INTERVAL(-WEEKDAY(mydate)) DAY);

更多信息

- Ruslan

---

不是的。我在英国，这里的一周从星期一开始（http://www.npl.co.uk/reference/faqs/which-is-the-first-day-of-the-week-and-which-is-week-1-of-the-year-(faq-time)）。 :) - Ruslan

我只是在谷歌上搜索了一下，说实话。但这很酷。但我的解决方案是为加拿大而设计的。 - davidahines

救了我的一天... - mehta-rohan

救了我的一天... - undefined

如果有人需要一周的最后一天，这里是 DATE_ADD(mydate, INTERVAL(6 -WEEKDAY(mydate)) DAY)。 - KailiC

Answer 2

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

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：

 

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

还有一个是WEEKDAY：

 

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

- Stéphane

---

这对我来说几乎可以工作。我想获取星期日的日期，因为这是我本周开始的第一天。所以如果我传入'2017-06-24'（星期六），FIRST_DAY_OF_WEEK将返回'2017-06-19'（星期一）。如何使它返回'2017-06-18'（星期日）？ - Jason Ayer

@JasonAyer 如果你还没有成功实现，你可以在函数内部执行 RETURN SUBDATE(day, DAYOFWEEK(day) - 1);。 - Joshua Skrzypek

Answer 3

如果一周从星期一开始

  SELECT SUBDATE(mydate, weekday(mydate));

如果一周从星期日开始

  SELECT SUBDATE(mydate, dayofweek(mydate) - 1);

例子：

SELECT SUBDATE('2018-04-11', weekday('2018-04-11'));

2018年4月9日

SELECT SUBDATE('2018-04-11', dayofweek('2018-04-11') - 1);

2018-04-08

- Egene

Answer 4

从周日开始计算，获取本周第一天和最后一天的日期。

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

从周一开始算起，则获取本周的第一天和最后一天日期。

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

- Ghanshyam Nakiya

Answer 5

select '2011-01-03' - INTERVAL (WEEKDAY('2011-01-03')+1) DAY; 

返回一周的第一天的日期。你可以深入了解一下。

- Cem Güler

---

如果输入的日期是一个星期天，它会将其回滚一周，而不是保留为那个星期天。 - Nikki

Answer 6

这种方法比编写函数来确定一周的第一天要简单得多。

一些变体可能会是：
SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY),INTERVAL 7 DAY)（用于查询的结束日期，例如在“开始日期”和“结束日期”之间）。
SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY（用于查询的开始日期）。

这将返回当前一周的所有值。一个示例查询如下：
SELECT b.foo FROM bar b
WHERE b.datefield BETWEEN
(SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY)
AND
(SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1)DAY),INTERVAL 7 DAY))

- Kenny_D

Answer 7

这对我有用

只需确保下面查询中的两个日期相同...

SELECT ('2017-10-07' - INTERVAL WEEKDAY('2017-10-07') Day) As `mondaythisweek`

这个查询返回：2017年10月2日，也就是星期一，

但如果你的第一天是星期日，那么只需从此结果减去一天即可！

- Paul Ishak

Answer 8

如果一周从星期一开始，请执行以下操作：

DATE_SUB(mydate, INTERVAL WEEKDAY(mydate) DAY)

- Павел П

Answer 9

  SELECT MIN(DATE*given_date*) FROM *table_name*

这将返回任何给定日期所在周开始的日期。

继续保持好的工作！

- Kamil Beeli de Belfort