Need mySQL TIMESTAMP for begining of the current week or any given date?

Question

Hi,

I need mySQL timestamp for start of current week or any given date, if week starts with monday?

I'm trying something like:

SELECT UNIX_TIMESTAMP(CONCAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) DAY), ' 00:00:00')) as start

Answer 1

Monday has a dayofweek index=2. DAYOFWEEK($date) gives the index of the day (1-7, Sun-Sat). So, you need to add or subtract days from your $date's index to change it to 2.

e.g.

SELECT UNIX_TIMESTAMP(
  CASE WHEN DAYOFWEEK($date)>=2 
    THEN DATE_SUB($date, INTERVAL (DAYOFWEEK($date)-2) DAYS) 
    ELSE DATE_ADD($date INTERVAL 1 DAY) 
  END
);

I think I've got the syntax right on this, but check CASE and date and time functions.