How to query a date range in mysql?

Asked

Viewed 7,106 times

2

I want to make a comparison if the date is within 30 days.

For example, I have the date 10/01/2016 and I want to return all results with dates 30 days next to this for example, 09/02/2016.

mysql

  • What kind of date are you using?! Date, Datetime, Timestamp?! Have to quote otherwise it gets difficult.

  • this with varchar but to convert to date and only use STR_TO_DATE(date, "%d/%m/%Y")

  • 30 days in what direction? + 30, -30, +15 and -15, +30 and -30? which one?

  • William, have you solved your problem with the answer? Or do you need some more information?

3 answers

4


I usually do this way below. I use NOW() to pick up the current date and subtract in the 30-day range using INTERVAL 30 DAY. In the query i do the comparison if the creation date (created_at of the kind timestamp) is bigger, so return me all the data: 

SELECT * FROM `task` WHERE created_at > (NOW() - INTERVAL 30 DAY)

So all results will be within 30 days.

  • This counts also in the month of 28, 29 days ?

  • @GOKUSSJ4 The question cites a comparison of an interval of 30 days, regardless of which month. This way I can get a result consistent with the question, based on my tests and considering timestamp.

  • 1

    @Funny GOKUSSJ4 that the most important questions people only ask in the comments. Then we have to deduce what the person means. I bet you’ve been through a lot. Hugs

  • Yes I know the question, but I would like to know if in the month of February it works also that way.

  • @GOKUSSJ4 30 days is 30 days buddy. If we have on March 1, counting 30 days back depending on the clear year, it will pass and fall in the month of January! Abs.

  • Cool, I didn’t know this way in mysql. + 1. but I think it’s 30 days forward.

  • The seamusd tip seems feasible, but if the client edits the date on his computer, it doesn’t affect the results ? or php will pick up the current date from some server ?

  • @Otaviofagundes current server date. Hugs

  • @Otaviofagundes a function NOW is independent of PHP. This is direct in query. Hugs

  • 1

    thanks for clearing my doubt, I was also in need of this

  • @Otaviofagundes just leave the +1 and it’s all right. Kkkkkkk briks. Needing only gives a shout here. Hugs

Show 6 more comments

2

You can use the expression;

SELECT * FROM `table` 
where STR_TO_DATE(data, "%d/%m/%Y") >= STR_TO_DATE('10/01/2016', "%d/%m/%Y")
and STR_TO_DATE(data, "%d/%m/%Y") <= STR_TO_DATE(09/02/2016', "%d/%m/%Y");

Or still use the between:

   SELECT * FROM `table` 
    where STR_TO_DATE(data, "%d/%m/%Y") between STR_TO_DATE('10/01/2016', "%d/%m/%Y")
  and STR_TO_DATE(09/02/2016', "%d/%m/%Y");

  • this does not work because accurate of all results 30 days between dataparametro2

  • It works, it just depends on what you pass in the parameters.

1

I managed to sort it out like this

SELECT * FROM PESSOAS WHERE DATEDIFF(STR_TO_DATE(ANIVERSARIO, "%d/%m/%Y"),STR_TO_DATE('10/01/2013', "%d/%m/%Y")) BETWEEN -30 and 30
  • 1

    So you only return 10 days...

  • I edited, but your tip helped me too, thank you very much

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.