Is it possible to set mySQL to have a saturday as the start of the week?

Is it possible to set mySQL to have a saturday as the start of the week?

I'm trying to run a query like:

SELECT DISTINCT(week(`date`)) 
  FROM `table` 
 WHERE `date` BETWEEN '2010-08-14' AND '2010-08-27'

But the week starts on a Saturday, not Sunday or Monday. (It's for a pay period week, not a regular week) It seems that the modes for the week function only offer Sunday or Monday开发者_运维知识库 as a starting date.

I've read that setting default_week_format can help but I don't think that will work for me as this is for specific reports only, not the entire system.

Do I have any other options?

Cribbed from the comments from the link in Tomasz's answer:

Problem: To find week start and end date with user specified start of the week day and user specified date for which the week is to be found.

date_sub(t.date, interval if(dayofweek(t.date)-$weekStartingDay >= 0, dayofweek(t.date)-$weekStartingDay, dayofweek(t.date)-$weekStartingDay+7) day) week_start

date_sub(t.date, interval if(dayofweek(t.date)-$weekStartingDay >= 0, dayofweek(t.date)-$weekStartingDay, dayofweek(t.date)-$weekStartingDay+7) - 6 day) week_end

I'll propose another decision which will allow you to manipulate and customize calendars between timezones and most important group by weeks which are defined in different time zones

so, let's assume that your mySQL is running on UTC configured server and you want to have a custom calendar which is 7 hours ahead and therefore your weeks should start on Saturday 7 am

CREATE TABLE `wh_blur_calendar` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `y` smallint(6) DEFAULT NULL,
  `q` tinyint(4) DEFAULT NULL,
  `m` tinyint(4) DEFAULT NULL,
  `d` tinyint(4) DEFAULT NULL,
  `w` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `wh_ints` (
  `i` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into wh_ints values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

now a popular Cartesian join which should populate your table:

INSERT INTO wh_blur_calendar (date)
SELECT DATE('2010-01-01 00:00:00 ') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM wh_ints a JOIN wh_ints b JOIN wh_ints c JOIN wh_ints d JOIN wh_ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) < 10245
ORDER BY 1;

Lets update the hours:

update  db_wh_repo.wh_blur_calendar set date = date_add(date, interval 7 hour);

and finally arrange your calendar's week in a custom way

UPDATE wh_blur_calendar
SET 
    y = YEAR(date),
    q = quarter(date),
    m = MONTH(date),
    d = dayofmonth(date),
    w = week(date_add((date), interval 1 day));

Believe me I spend some hours coming up with this decision but it gives you so much freedom in case you want to group your results based on a custom time-zone and week definitions.

It is possible to have Monday or Sunday, AFAIK there is no other option:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html