title: MYSQL Get the date from the current year, current month and variable day of the week
body: <p>I need to get a series of dates by using the day of the week and the time as the only bit of information.</p>
  
  <p>I am aware of the reverse of this operation, that is using WEEKDAY(date) or DAYOFWEEK(date), but this is not what I need.</p>
  
  <p>I have a table, which looks like this, which will be continuously scanned and compared to the current date.</p>
  
  <pre><code>+----+---------+----------+----------+
  | id | weekday | start    | end      |
  +----+---------+----------+----------+
  |  1 |       1 | 09:00:00 | 17:00:00 |
  |  2 |       2 | 09:00:00 | 17:00:00 |
  |  3 |       3 | 09:00:00 | 17:00:00 |
  |  4 |       4 | 23:00:00 | 17:00:00 |
  |  5 |       5 | 18:00:00 | 23:00:00 |
  |  6 |       6 | 00:00:00 | 00:00:00 |
  |  7 |       7 | 00:00:00 | 01:00:00 |
  +----+---------+----------+----------+
  </code></pre>
  
  <p>The above is the simple version, I expect this table to have hundreds or thousands of possible iterations. As observed on the 4th row, the times may also go over midnight, and I must compensate.</p>
  
  <p>I need a query which will provide me with the following result sets, and am having trouble with locking down onto the date, based by the current year, month and the passed day of the week from the weekday column.</p>
  
  <p>Example result set:</p>
  
  <pre><code>+---------------------+---------------------+
  | start               | end                 |
  +---------------------+---------------------+
  | 2013-06-24 09:00:00 | 2013-06-24 17:00:00 |
  | 2013-06-25 09:00:00 | 2013-06-25 17:00:00 |
  | 2013-06-26 09:00:00 | 2013-06-26 17:00:00 |
  | 2013-06-27 23:00:00 | 2013-06-28 17:00:00 | # &lt;---- Notice the date difference here
  | 2013-06-28 18:00:00 | 2013-06-28 23:00:00 |
  | 2013-06-29 00:00:00 | 2013-06-30 00:00:00 | # &lt;---- Also here, the span is 24 hours
  | 2013-06-30 00:00:00 | 2013-06-30 01:00:00 |
  +---------------------+---------------------+
  </code></pre>
  
  <p>Any help would be appreciated, I have gotten as close to getting the CURRENT DATE only, however, this poses some problems to me, as these dates later need to be converted, so I need an array of all of them, not just the current day's.</p>
  
  <p><strong>Edit: Posted my current code: Though I do not believe this is quite headed in the right direction</strong></p>
  
  <pre><code>SELECT  TIMESTAMP(IF (end &lt; start,
                       CURRENT_DATE,
                       SUBDATE(CURRENT_DATE, 1)),
                    start) AS 'start',
  
          TIMESTAMP(IF (end &lt; start,
                       CURRENT_DATE,
                       SUBDATE(CURRENT_DATE, 1))
                    end) AS 'end',
  
    FROM  shift
  </code></pre>
  
  <p>And the result:</p>
  
  <pre><code>+---------------------+---------------------+
  | start               | end                 |
  +---------------------+---------------------+
  | 2013-06-27 23:00:00 | 2013-06-28 17:00:00 | 
  | 2013-06-28 18:00:00 | 2013-06-28 23:00:00 | # Needs the rest of the dates of the week
  +---------------------+---------------------+
  </code></pre>
  
  <p><strong>SECOND EDIT</strong></p>
  
  <p>Thanks to the below commenters' help, I have arrived at the below query, moving forward though, some have expressed concern with the design and I will be running some tests before I settle on an answer. If there is a better way to get this done, I would be really interested in hearing it!</p>
  
  <p>I suppose the best point is when the month changes half way through the query, using the day of the week to determine the difference might be troublesome.</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT  @diff:=(CAST(weekday AS SIGNED) - (WEEKDAY(CURRENT_DATE) + 1)) as 'diff',
          @date:=DATE_ADD(CURRENT_DATE, INTERVAL @diff DAY) as 'start_date',
          TIMESTAMP(@date, start) AS 'start',
          TIMESTAMP(IF (end &lt;= start,
                       DATE_ADD(@date, INTERVAL 1 DAY),
                       @date),
                    end) AS 'end'
   FROM   shift
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>+------+------------+---------------------+---------------------+
  | diff | start_date | start               | end                 |
  +------+------------+---------------------+---------------------+
  |   -4 | 2013-06-24 | 2013-06-24 09:00:00 | 2013-06-24 17:00:00 |
  |   -3 | 2013-06-25 | 2013-06-25 09:00:00 | 2013-06-25 17:00:00 |
  |   -2 | 2013-06-26 | 2013-06-26 09:00:00 | 2013-06-26 17:00:00 |
  |   -1 | 2013-06-27 | 2013-06-27 23:00:00 | 2013-06-28 17:00:00 |
  |    0 | 2013-06-28 | 2013-06-28 18:00:00 | 2013-06-28 23:00:00 |
  |    1 | 2013-06-29 | 2013-06-29 00:00:00 | 2013-06-30 00:00:00 |
  |    2 | 2013-06-30 | 2013-06-30 00:00:00 | 2013-06-30 10:00:00 |
  +------+------------+---------------------+---------------------+
  </code></pre>
  
  <p><strong>Third Edit</strong></p>
  
  <p>I have tested the above query over periods which span across the month's beginnings and ends and have found it to be suitable for its purpose, I will move the above query as well as some notes to an answer in hopes that someone may find this useful in future.</p>
  
  <p>On that note, I will also include the various gotchas and recommendations from the various commenters, again thanks to everyone who took the time to help.</p>