mysql 当前周 第一天_如何在mysql中获取一周的第一天？

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,

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.

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