CODEWITHSUNDEEP
phpmysql
user1032531
user1032531

Reputation: 26281

Return all records for given week

I wish to return all records for the 5th week of 2012. For instance, the following query would provide the correct records, however, is very inefficient as WEEK() and YEAR() must be calculated for each row and indexes can't be used. I expect BETWEEN should be used, but I am not sure how I would do so. I also need to do something similarly for a given month and quarter, and ideally could use a similar solution. If need be, I could supplement the SQL with PHP. Thanks

SELECT * FROM myTable WHERE WEEK(dateColumn)=5 AND YEAR(dateColumn)=2012;

EDIT - THE FOLLOWING HAS BEEN ADDED TO SUPPLEMENT COMMENTS

*** TEST DATA *** 

SELECT * FROM projects_sell WHERE award_date BETWEEN '2012-04-01' AND '2012-04-08'

0.55236601829529

SELECT * FROM projects_sell WHERE YEAR(award_date)=2012 AND WEEK(award_date)=5

0.52925586700439

SELECT * FROM projects_sell WHERE award_date > STR_TO_DATE('1,5,2012', '%w,%v,%Y') AND award_date < STR_TO_DATE('0,5,2012', '%w,%v,%Y')

0.69798302650452

*** TEST SCRIPT *** 

//$sql ="SELECT * FROM projects_sell WHERE award_date BETWEEN '2012-04-01' AND '2012-04-08'";
//$sql ="SELECT * FROM projects_sell WHERE YEAR(award_date)=2012 AND WEEK(award_date)=5";
$sql ="SELECT * FROM projects_sell WHERE award_date > STR_TO_DATE('1,5,2012', '%w,%v,%Y') AND award_date < STR_TO_DATE('0,5,2012', '%w,%v,%Y')";

$current_time=microtime(true);
for ($i=1; $i<=1000; $i++)
{
    $stmt = db::db()->query($sql);
    $rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
echo($sql.'<br>'.(microtime(true)-$current_time).'<br>');

Upvotes: 0

Views: 69

Answers (2)

colin-higgins
colin-higgins

Reputation: 1117

you could figure out the beginning date and the end date of the fifth week, and filter based on the BETWEEN operator

SELECT * FROM Table1 WHERE T.Date1 BETWEEN '01-29-2012 00:00:00' AND '02-04-2012 00:00:00'

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33511

Using STR_TO_DATE you can create the first and last day of a week. Then use BETWEEN or date arithmetic to find the dates:

WHERE dateColumn > STR_TO_DATE('1,5,2012', '%w,%v,%Y') 
               AND dateColumn < STR_TO_DATE('0,5,2012', '%w,%v,%Y')

Upvotes: 1

Related Questions