mysql仅选择下周数据 [英] mysql select only next week data

问题描述

我的数据库中有一个日期字段，即film_release_date.

I have a date field in my database i.e film_release_date.

那么从表中获取下周数据的mysql查询是什么.

So what is mysql query to fetch the next week data from table.

我运行了此查询，但是无法正常工作.

I run this query but its not working properlly.

SELECT 
    *,
    DATE_FORMAT(film_release_date,'%e-%b-%Y') AS release_date 
FROM gf_film 
WHERE 
    MONTH(film_release_date)=MONTH(CURRENT_DATE) 
    AND YEAR(film_release_date)=YEAR(CURRENT_DATE) 
    AND film_release_date>=(CURRENT_DATE) 
ORDER BY film_release_date DESC 

实际上，对于电影网站来说，我的数据库中有几部电影，因此我需要一个查询，该查询每隔一周抓取一次电影，这意味着下周将发行的电影意味着下一个星期五.如果我今天运行此电影(即13日)，那么它将显示所有内容15到22之间的电影，如果在15到22之间运行，则会显示22到29之间的数据

Actually its for movie website i have several movies in my database so i need a query which fetch every next week movie that means movies that will release in next week means next friday.If i run this today i.e 13 then it shows all movies between 15 to 22 and if run this between 15 to 22 then it shows data from 22 to 29

推荐答案

set @d := date_add(CURRENT_DATE, interval 7 day);
set @week_start := @d - interval (dayofweek(@d) + 1) day;
set @week_end := @d + interval (6 - dayofweek(@d)) day;
SELECT *,
       DATE_FORMAT(film_release_date,'%e-%b-%Y') AS release_date
FROM gf_film
WHERE film_release_date between @week_start and @week_end
ORDER BY film_release_date DESC

这篇关于mysql仅选择下周数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！