CODEWITHSUNDEEP
phpmysqlsqldateweb
confused_geek
confused_geek

Reputation: 259

get all the past 7 dates from the current date in mysql

How to show all the dates from the past 7 days starting from the current day . For eg : if today is 10/3/2018 . I want the output to be 

10/3/2018
9/3/2018
8/3/2018
7/3/2018
6/3/2018
5/3/2018
4/3/2018

Now the main thing is that my table does not have entry for all the dates .So if i use between or something like that . it will show me only the dates that are available in the table.

I am actually trying to fetch the data for the past 7 days and if nothing is available for that day then zero or blank should come.

EDIT 1:

Select curdate()
union Select date_sub(Curdate(),interval 1 day) 
union Select date_sub(Curdate(),interval 2 day) 
union Select date_sub(Curdate(),interval 3 day) 
union Select date_sub(Curdate(),interval 4 day)
union Select date_sub(Curdate(),interval 5 day) 
union Select date_sub(Curdate(),interval 6 day)
from detail

Now this is returning all the previous dates like i wanted , but i am still not able to group it properly with the where clause . So that the dates return the number of orders placed on that day and return 0 if nothing is found .

SELECT count(id) from detail where status in(3,7).

This was the original query

Upvotes: 0

Views: 790

Answers (2)

Vijaya Vignesh Kumar
Vijaya Vignesh Kumar

Reputation: 444

You NO need to add any date in any tables

you can get last seven days using UNION like below

Select if(created_at,created_at,0) as past_seven_days from (

    Select curdate() as past_seven_days
          union
    Select date_sub(Curdate(),interval 1 day)
          union
    Select date_sub(Curdate(),interval 2 day)
          union
    Select date_sub(Curdate(),interval 3 day)
          union
    Select date_sub(Curdate(),interval 4 day)
          union
    Select date_sub(Curdate(),interval 5 day)
         union
    Select date_sub(Curdate(),interval 6 day)
         union
    Select date_sub(Curdate(),interval 7 day) )as p

left join url as u on p.past_seven_days = u.created_at

Ouput

Upvotes: 2

TarangP
TarangP

Reputation: 2738

One solution is to create a calendar table containing all the dates you need. You can then left join it to your data to get what you are after

Upvotes: 1

Related Questions