StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORetrieve rows where their stored time frame has one or more occurrences of a given week day
text
Body
copied!<p>I would like to know the best way (performance) to retrieve rows from a MySQL table, where two date type fields, who together compose a given time frame, happen to have a certain week day somewhere among the time frame in question.</p> <p>Assuming the given table structure and data example, what would be the best way of retrieving all rows where their stored time frame happens to have one or more occurrences of Monday for example, where Monday is referenced by the number 2. </p> <pre><code>Table: ExampleA Fields: ID (int) | Begin (date) | End (date) ------------------------------------ 1 | 2012-02-14 | 2012-02-16 2 | 2012-01-01 | 2012-01-15 3 | 2012-03-04 | 2012-03-06 4 | 2012-01-23 | 2012-02-07 5 | 2012-07-15 | 2012-07-15 6 | 2012-03-06 | 2012-03-13 </code></pre> <p>In example query it would be preferred to reference the given week day by a non-zero based index, starting from Sunday ending on Saturday, composing the following index positions: 1 to 7 respectively. (Same as the MySQL function <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek" rel="nofollow noreferrer">DAYOFWEEK</a>.)</p> <p><strong>UPDATE:</strong></p> <p>Been looking over some way on how to iterate day by day through a given time frame and doesn't look good. The requirement to create Temporary tables and such, not practical and when simultaneous users request a action with the same requirement it would cause problems.</p> <p>Example: <a href="https://dba.stackexchange.com/questions/8155/how-can-i-get-a-list-of-dates-between-two-given-dates-with-mysql">How can I get a list of dates between two given dates with mysql?</a></p> <p>The initial idea was to loop through each day of the time frame of each row. Then for each time frame go through the initial date to the end date day by day and return a list of which week days were found for each time frame (row). Not a fast way for sure, but just something to get started. Any recommendations or other suggestions better than this one?</p> <p><strong>UPDATE 2:</strong></p> <p><a href="https://stackoverflow.com/users/1621209/g-nugget">G-Nugget</a> <a href="https://stackoverflow.com/a/14283551/114298">suggested</a> creating seven boolean fields one for each week day, and when adding a new row, mark the appropriate fields true for the week days found in the time frame of said row. Of course this being done by the application using the database, not the SQL query itself.</p> <p>It is the most straightforward and least performance impacting solution I've though or seen to this time, however I cannot edit the table schema.</p> <p><strong>UPDATE 3:</strong></p> <p>To clarify this question even more, in the event someone is confused or misreads it. Note:</p> <blockquote> <p>I'm looking to retrieve the rows where you can find the week day Monday or another week day of my choosing somewhere in their time frame (going from "Being" to "End"), either at the "Begin", "End", or along the period of time ranging from "Begin" to "End"</p> </blockquote> <p><strong>UPDATE 4:</strong></p> <p>Here you have a <a href="http://sqlfiddle.com/#!2/74de7/8/0" rel="nofollow noreferrer">SQL Fiddle</a> of this question for you to try your ideas, without having to build the table in your machine.</p> <p><strong>UPDATE 5 - Solution:</strong></p> <p>Using <a href="https://stackoverflow.com/users/1029908/alexey-gerasimov">Alexey Gerasimov</a> <a href="https://stackoverflow.com/a/14284165/114298">answer</a> I was able to apply his where clause logic to my own problem and work through my solution. Thanks Alexey.</p> <p>If your reading this and want to try it and see if it suits your needs:</p> <p>You can use this <a href="http://sqlfiddle.com/#!2/74de7/14/1" rel="nofollow noreferrer">SQLFiddle</a> or this small query to setup and run his solution:</p> <pre><code># Create table CREATE TABLE `ExampleA` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Begin` date NOT NULL, `End` date NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1; # Insert demo data INSERT INTO `ExampleA`(`ID`,`Begin`,`End`) VALUES (1,'2012-02-14','2012-02-16'), (2,'2012-01-01','2012-01-15'), (3,'2012-03-04','2012-03-06'), (4,'2012-01-23','2012-02-07'), (5,'2012-07-15','2012-07-15'), (6,'2012-03-06','2012-03-13'); # Query to retrieve rows which time frame # has one or more occurrences of a given weekday # # 1 Sunday # 2 Monday # 3 Tuesday # 4 Wednesday # 5 Thursday # 6 Friday # 7 Saturday SET @MYDAYOFWEEK=2; SELECT * FROM ExampleA WHERE ( ( @MYDAYOFWEEK >= DAYOFWEEK(BEGIN) AND @MYDAYOFWEEK <= DAYOFWEEK(BEGIN) + DATEDIFF(END,BEGIN) ) OR ( @MYDAYOFWEEK+7 >= DAYOFWEEK(BEGIN) AND @MYDAYOFWEEK+7 <= DAYOFWEEK(BEGIN) + DATEDIFF(END,BEGIN) ) ) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload