Query weekly meetings in MySql

In one of my current projects, I wanted to create a view box that shows a list of regular meetings in the current week. The meeting table was:

id title description startdate enddate
1 Meeting 1 ….. 17/10/2009 19/10/2024
2 Meeting 2 ….. 18/10/2009 18/11/2009
3 Meeting 3 ….. 14/10/2009 18/11/2010


Assuming that the current date is 22/10/2009, then the view box will show the records with ids (1 and 2). Where as the record with the id 3 is for a past meeting on Wednesday (21/10/2009).

First attempt

 SELECT * FROM meetings as m 
 	WHERE DAYOFWEEK(startdate) 
 		BETWEEN DAYOFWEEK(NOW()) AND '7' 
        AND enddate > NOW()
 	ORDER BY startdate ASC

This query will do what I want, but it has one drawback. DAYOFWEEK() function return 1 for Sunday, but in New Zealand the first day of the week is Monday.

My second attempt

  SELECT * FROM meetings as m 
  	WHERE IF(DAYOFWEEK(startdate) = 1, 7, DAYOFWEEK(startdate)-1) 
  		BETWEEN 
  	      IF(DAYOFWEEK(NOW()) = 1, 7, DAYOFWEEK(NOW())-1) 
  	        AND '7' 
                AND enddate > NOW()
 	ORDER BY startdate ASC

This query is the same as the first one, the differences are on line 2 and 4.

On line 2, instead of directly getting the index value of the startdate, I have added an if statement to check if the current index is equal to 1 (Sunday), then change it to 7 (last day of the week). For all the other days in the week minus 1 from the index.

	WHERE IF(DAYOFWEEK(startdate) = 1, 7, DAYOFWEEK(startdate)-1) 

Line 4 is the same as line 2.

	IF(DAYOFWEEK(NOW()) = 1, 7, DAYOFWEEK(NOW())-1) 

This query will return the result I want 🙂

Your comments are welcome.

Posted in MySql and tagged .
Loading Facebook Comments ...

Leave a Reply

Your email address will not be published. Required fields are marked *

2 Comments

  1. Hi Mohammed,

    Have you tried setting the time zone for MySQL?

    The default time zone in MySQL is SYSTEM. If your system default is UTC then your DayOfWeek will be out by 12/13 hours (depending on daylight savings time).

    To check your time zone in MySQL:
    select @@time_zone;

    To change your time zone to New Zealand:
    set time_zone=”NZ”;