-1

Need help here, having an mysql table called APPROVAL, there having an id,dateandtime and level, i need a query that selects the id alone with the following condition.

Taking date alone from database and comparing it with current system date, if the days exceeds above 30 and below 60 and also level = 5.

How can I write a query for this.

Thanks in advance.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Crysis
  • 418
  • 2
  • 7
  • 28
  • Sry sir i dont have idea how to write this query, iam a bit newbie here. – Crysis Sep 11 '15 at 10:58
  • I won't downvote you, but I suggest you to edit your question because it hardly clear.. ( and usually people say hello and thanks when they are in need ) – CDF Sep 11 '15 at 11:00
  • So ( as it is still not that clear ...) you want to retrieve the id of the records that match level = 5 and if the date difference is between 30 and 60, don't you? – CDF Sep 11 '15 at 11:08
  • Yes sir you are correct :) – Crysis Sep 11 '15 at 11:10
  • Ok below the query, test it on your script and revert ;) have a nice day! – CDF Sep 11 '15 at 11:16

3 Answers3

2

MySQL has good date arithmetic. For example, the expression

 CURDATE() + INTERVAL 30 DAY

gives a datetime value denoting midnight 30 days hence. Similarly 

CURDATE()  + INTERVAL 61 DAY

yields midnight on the 61st day.

So a query of the form

   SELECT ID
     FROM APPROVAL
    WHERE Level = 5
      AND `DateTime` >=  CURDATE() + INTERVAL 30 DAY
      AND `DateTime` <   CURDATE() + INTERVAL 61 DAY

will yield what you want. Notice the use of >= for the beginning of the range of days, and the use of < and an extra day for the end of the range. We do that because we want all items from the 60th day, and none from the 61st day.

A compound index on (Level, DateTime) will make this query very efficient to satisfy.

Notice that an expression like

    DATE(`DateTime`) <=   CURDATE() + INTERVAL 60 DAY  /* slow! */

will also yield correct results, but the presence of the the DATE() function call on the column to be searched makes it unsargeable. That is, it makes MySQL unable to use an index to satisfy the search.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thank u sir for u quick response, how can i write this query in the form of PHP code igniter framework. – Crysis Sep 11 '15 at 11:32
  • @Crysis How to put it into codeigniter is a different question. You probably should do a bit of research and if you can'f figure it out, ask that question separately. At any rate I'm not a codeIgniter user so I don't know. – O. Jones Sep 11 '15 at 11:34
1

Ok so use this query to retrieve all the IDs that match level 5 and date diff between 30 and 60 compared to the current date.

SELECT id
FROM APPROVAL
WHERE level = 5 && DATEDIFF(NOW(), dateandtime) BETWEEN 30 AND 60

I'd suggest you to order them dy date DESC too.

Hope that helps

CDF
  • 194
  • 5
0

I hope, I understood your problem correctly.

select `ID`
from APPROVAL
where `Level` = 5
    and ( DATE(`DateTime`) > curdate() + interval 30 day 
        and DATE(`DateTime`) < curdate() + interval 60 day )
order by `ID` asc;

Where DATE() gets the date from a datetime and CURDATE() is the current system date. With interval you can manipulate a date expression whitout having to worry about its limits.

Derenir
  • 537
  • 1
  • 12
  • 29