Mysql query based on date?

Question

I have a table like this

id | date | content
1  | 09-16-2013 | content 1 here
2  | 09-23-2013 | content 2 here 
3  | 09-30-2013 | content 3 here

I would like to display the content for a week from that date. For example, the first content should start on 9/16/2013 and then show until 9/22/2013 mid night. then on next day, it changes to the content 2. Same way,when I am on content 2, I want to display like "previous week content" and then show just the previous ones..I think I can do this by checking the current date and then anything below that has to be displayed.

I am not very good at these kind of mysql queries, please advise!

Regards

yes if the table has today's date then for a weeek from that date. — JDesigns
– JDesigns, Commented Sep 19, 2013 at 18:55

peterm · Accepted Answer · 2013-09-19 21:06:56Z

1

I guess you're looking for something like this

SELECT * 
  FROM table1
 WHERE date BETWEEN CURDATE() + INTERVAL 0 - WEEKDAY(CURDATE()) DAY
                AND CURDATE() + INTERVAL 6 - WEEKDAY(CURDATE()) DAY

This query will grab a row(s) where date column is within the boundaries of the current calendar week (from Monday to Sunday).

WEEKDAY() function returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday). The expression

CURDATE() + INTERVAL 0 - WEEKDAY(CURDATE()) DAY

returns a date for Monday of the current calendar week and

CURDATE() + INTERVAL 6 - WEEKDAY(CURDATE()) DAY

returns a date for Sunday of the current calendar week. Using BETWEEN in WHERE clause makes sure that a query returns only rows with date values that falls between these two dates (Monday through Sunday).

Note: Make sure that you have an index on date column. This query is index-friendly.

Sample output for today's date (09/19/2013):

+------+------------+----------------+
| id   | date       | content        |
+------+------------+----------------+
|    1 | 2013-09-16 | content 1 here |
+------+------------+----------------+

UPDATE: To get records for previous calendar week you just substract 1 week interval from both values in BETWEEN

SELECT * 
  FROM table1
 WHERE date 
   BETWEEN CURDATE() + INTERVAL 0 - WEEKDAY(CURDATE()) DAY - INTERVAL 1 WEEK,
       AND CURDATE() + INTERVAL 6 - WEEKDAY(CURDATE()) DAY - INTERVAL 1 WEEK

edited Sep 19, 2013 at 21:06

answered Sep 19, 2013 at 19:10

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

JDesigns Over a year ago

I think this is what I need! Will check and get back here to vote up!

JDesigns Over a year ago

can you please add little explanation of how this query works ..?

JDesigns Over a year ago

thank you @peterm ! I will check it with several records and test it. Thanks again!

peterm Over a year ago

@Jay You're very welcome :) See updated answer for some explanation.

JDesigns Over a year ago

Thank you! it helps. One last question. For my question where I want to show the previous week's content, should I just manipulate the BETWEEN condition? For example, next week Monday, I should show this week's content . same way , 3 weeks from now I should show past two weeks under "previous week" section along with that current week's content.

|

EliteTech · Accepted Answer · 2013-09-19 18:51:12Z

0

Try this

SELECT * FROM table WHERE date BETWEEN '09-16-2013' AND '09-22-2013';

answered Sep 19, 2013 at 18:51

EliteTech

3863 silver badges13 bronze badges

1 Comment

JDesigns Over a year ago

I cannot have the date in the query..this is like quote of the week so have all records in the mysql table for each week ...so for example, every monday i will have a new quote so from that date until 1 week need to show the same content.

Tufan Barış Yıldırım · Accepted Answer · 2013-09-19 18:52:09Z

0

keyword is WEEK()

SELECT id,date, CONCAT('content ',WEEK(date),' to here') as content  FROM table_name

answered Sep 19, 2013 at 18:52

Tufan Barış Yıldırım

3,97228 silver badges37 bronze badges

Comments

Ankit Agrawal · Accepted Answer · 2013-09-19 18:52:32Z

0

SELECT * 
FROM table
WHERE date BETWEEN '9/16/2013 00:00:00.00' AND '9/22/2013 00:00:00.00'

answered Sep 19, 2013 at 18:52

Ankit Agrawal

6,1246 gold badges27 silver badges49 bronze badges

Comments

kcsoft · Accepted Answer · 2013-09-19 19:05:04Z

0

You can replace the week offset to your needs

SET @weekOffset = +2;
SELECT * FROM test
WHERE WEEK(`date`) = WEEK(NOW()) + @weekOffset;

See a working demo here

answered Sep 19, 2013 at 19:05

kcsoft

2,94722 silver badges15 bronze badges

Comments

Sean · Accepted Answer · 2013-09-19 19:16:28Z

0

To select it dynamically, try something like

SELECT * FROM `yourTable` WHERE NOW() >= STR_TO_DATE(`date`, '%m-%d-%Y') ORDER BY STR_TO_DATE(`date`, '%m-%d-%Y') DESC LIMIT 1

or t

SELECT * FROM `yourTable` WHERE CURDATE() >= STR_TO_DATE(`date`, '%m-%d-%Y') ORDER BY STR_TO_DATE(`date`, '%m-%d-%Y') DESC LIMIT 1

sqlfiddle example - http://sqlfiddle.com/#!2/62982/4

edited Sep 19, 2013 at 19:16

answered Sep 19, 2013 at 18:59

Sean

12.4k3 gold badges31 silver badges49 bronze badges

1 Comment

Sean Over a year ago

Also, if you remove the LIMIT 1 from these queries, it will return all the previous 'contents' in the same query - sqlfiddle.com/#!2/62982/5. You could also change it to LIMIT 2 to get just the current and previous week - sqlfiddle.com/#!2/62982/6

Collectives™ on Stack Overflow

Mysql query based on date?

6 Answers 6

7 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

7 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related