Clever Geek Handbook

Can I install mySQL on Saturday as the start of the week?

Can I install mySQL on 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 Saturday, not Sunday or Monday. (This is the week of the pay week, not the normal week.) The modes for the week function seem to only suggest Sunday or Monday as the start date.

I read that setting default_week_format might help, but I don't think it will work for me as it only affects specific reports and not the whole system.

Do I have other options?

+2


source to share


3 answers


Cribbed from the comments on the link in Tomasz:

Problem: Find start and end of week date with user specified start day of week and user specified date for which should 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
+1


source


I will suggest another solution that will allow you to manipulate and customize calendars between time zones and the most important weekly groups that are defined in different time zones.

So, let's say your mySQL is running on a UTC server and you want your own calendar that is 7 o'clock ahead and so your weeks should start on Saturday 7am.

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);

is now a popular cartesian join that 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;


Allows you to update your watch:

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

and finally, arrange the calendar week in your own 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));

Trust me, I spend several hours coming up with this solution, but it gives you so much freedom if you want to group your results based on custom timezone and week definitions.

+1


source


Maybe you have Monday or Sunday, AFAIK there is no other option:

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

0


source






More articles:


All Articles