基于日期的Mysql查询？

问题描述：

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

我有一个这样的表 p>

  id | 日期| 内容
1 |  09-16-2013 | 内容1在这里
2 |  09-23-2013 | 内容2在这里
3 |  09-30-2013 | 内容3在这里
  code>  pre> 
 
 我想显示该日期一周内容。 例如，第一个内容应该从2013年9月16日开始，然后显示到2013年9月22日深夜。 然后在第二天，它改为内容2. 
同样的方式，当我在内容2时，我想显示像“上周内容”然后只显示以前的那些..我想我可以通过检查来做到这一点 当前日期，然后是必须显示的任何内容。 p> 
 
 
我不是很擅长这些mysql查询，请指教！ p> 
 
 
 问候 p> 
  div>

答

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

答

Try this

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

答

keyword is WEEK()

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

答

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

答

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

答

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

相关推荐