Select current week, starts from Monday instead of Sunday

I’ve managed to select the data from the current week but the week itself starts from Sunday which is not the right format for me, it should starts from Monday. I’m using MySQL to query the data.

SELECT IFNULL(SUM(rendeles_dbszam),0) as eladott_pizzak_szama 
  FROM rendeles 
 WHERE WEEK(rendeles_idopont) = WEEK(CURRENT_DATE())

Answer

You can use this little formula to get the Monday starting the week of any given DATE, DATETIME, or TIMESTAMP object.

FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -2, 7))

I like to use it in a stored function named TRUNC_MONDAY(datestamp) defined like this.

DELIMITER $$

DROP FUNCTION IF EXISTS TRUNC_MONDAY$$
CREATE
  FUNCTION TRUNC_MONDAY(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'preceding Monday'
  RETURN FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -2, 7))$$

DELIMITER ;

Then you can do stuff like this

SELECT IFNULL(SUM(rendeles_dbszam),0) as eladott_pizzak_szama
  FROM rendeles
 WHERE TRUNC_MONDAY(rendeles_idopont) = TRUNC_MONDAY(CURRENT_DATE())

or even this to get a report covering eight previous weeks and the current week.

SELECT SUM(rendeles_dbszam) as eladott_pizzak_szama,
       TRUNC_MONDAY(rendeles_idopont) as week_beginning
  FROM rendeles
 WHERE rendeles_idopont >= TRUNC_MONDAY(CURDATE()) - INTERVAL 8 WEEK
   AND rendeles_idopoint < TRUNC_MONDAY(CURDATE()) + INTERVAL 1 WEEK
 GROUP BY TRUNC_MONDAY(rendeles_idopont)

I particularly like this TRUNC_MONDAY() approach because it works unambiguously even for calendar weeks that contain New Years’ Days.

(If you want TRUNC_SUNDAY() change the -2 in the formula to -1.)