Select query - between dates

PHP
1

Hi,
I am trying to select from a database all monitor_types that have a monitor_dueDate between todays date and todays date minus 1 month?

e.g monitor_dueDate between 2010-11-10 and 2010-10-10

I have come up with this but dont know if I am on the right track at all.


$query="SELECT monitor_type, monitor_owner, monitor_freq, monitor_completedDate, monitor_dueDate, monitor_email
FROM monitoring
WHERE monitor_dueDate BETWEEN $todaysdate AND DATE('$todaysdate')- 1 MONTH";

Any help would be great.

Thanks,

Jordan

2

Try this where SUBDATE can minus one month or 31days, bare in mind that some months have only 28 days. PHP can do it aswell:


$query="SELECT monitor_type, monitor_owner, monitor_freq, monitor_completedDate, monitor_dueDate, monitor_email 
FROM monitoring 
WHERE monitor_dueDate = SUBDATE('.$todaysdate.', INTERVAL 31);

PHP code way with MySQL query:


$whenDaysHenceInSecs = strtotime("- 1 MONTH");
$expire_date = date("Y-m-d", $whenDaysHenceInSecs);

$query="SELECT monitor_type, monitor_owner, monitor_freq, monitor_completedDate, monitor_dueDate, monitor_email 
FROM monitoring 
WHERE monitor_dueDate = "'.$expire_date.'" );
3

you’re on the wrong track :slight_smile:

when you say WHERE x BETWEEN this AND that, it is exactly the same as if you said WHERE x >= this AND x <= that

if these were numbers, then WHERE x BETWEEN 25 AND 15 would be equivalent to WHERE x >= 25 AND x <= 15

if you look at that last one closely, you’ll see it’s impossible

and the reason is, because the smaller value must come first

with dates, the earlier value must come first

so what you want is

WHERE monitor_dueDate >= CURRENT_DATE - INTERVAL 1 MONTH
  AND monitor_dueDate <= CURRENT_DATE

this is the way i prefer to write date range tests, rather than using BETWEEN, but i won’t go into why until you’ve understood everything so far

4

Thanks for the replies.
I have got it working with your help.


$todayDate =  date('Y-m-d');
$minusDate1 = date("Y-m-d", strtotime("$todayDate -1 month"));
$minusDate2 = date("Y-m-d", strtotime("$todayDate -2 month"));

$q = "SELECT monitor_id
FROM monitoring
WHERE monitor_dueDate <= '$minusDate1' AND monitor_dueDate >= '$minusDate2'";

Thanks again.

5

I would also like to hear why you dont use BETWEEN, if you have the time. Just to help build up my knowledge.

Thanks

6

that works, but it isn’t “between todays date and todays date minus 1 month” as in your original post

:slight_smile:

7

because the upper end of the range needs to be open

for example, to get all the rows for today, you could do this –


WHERE dt BETWEEN '2010-11-12 00:00' AND '2010-11-12 23:59'

however, this will not pick up any times after 23:59, e.g. 23:59:37

so the better choice is ----


WHERE dt >= '2010-11-12' 
  AND dt  < '2010-11-13'

see how that works?

an even better example is to pull all the datetimes for a month

could you please do the BETWEEN for november 2010? then do it again using an open upper end on the range

8

Why not simply do;

WHERE dt BETWEEN '2010-11-12 00:00:00' AND '2010-11-12 23:59:59'

?

As far as I have noticed it will pick up all rows in that range

9

Like this?


WHERE dt BETWEEN '2010-11-01' AND '2010-11-30'



WHERE dt >= '2010-11-01' 
AND dt  < '2010-12-01'
10

yes, exactly, you understand the difference, nice job :slight_smile:

okay, two questions

  1. would 2010-11-30 09:37 be returned by both of those?

  2. do you really feel like calculating the last day of any month? and checking for leap years? isn’t just calculating the 1st of the following month easier?

now you know why i don’t use BETWEEN

:cool: