Date Comparison

[tobyheywood]

Dear All,

I am in the process of writing a small bit of code, which will look at a date pulled in from a MySQL database and then compare it with the current date.

now I am a little unsure as to which will be the best route to take; should I do the date comparison using PHP or should I alter my MySQL query to use something like datediff()?

If anyone would like to offer some advise on this it would be greatly appreciated.

Toby Heywood

 

[LTeeple]

You can use NOW() in mysql.

So, if you wanted to pull all PAST dates from a mysql do something like:

select dates from tablename where dates < NOW();

If that's not quite what you're looking for, give us your specific scenario. We could better help.


Cheers!
Laura

 

[tobyheywood]

I think DATEDIFF(date, date2) is probably best as it returns the number of days between the two dates, but I'm unsure as to how to include it in a select statement.

Basically I want to change the format of some text on a page (ie highlight or bold) when the date returned by NOW() is within x days of the renewal date contained in the table.



Toby Heywood

 

[LTeeple]

Okay I understand your scenario. You can modify the below code to suit your specific needs:

$sql = 'select * from tablename';
$results = mysql_query($sql,$connection)
    or die('Couldn't execute query');
$today = time();
while ($row = mysql_fetch_array($results)){
  $datefield = $row['DATEFIELD'];
  $comparison_date = strtotime($datefield );
  $datediff =  $comparison_date - $today ;
  $number_days_difference = $datediff/86400;
  $number_days_difference = ceil($number_days_difference);
  $string = '<strong>Date returned is within ' . $number_days_difference . " days of today's date.</strong>";
  echo $string;
}

1. $today = time in seconds since unix epoch (January 1 1970 00:00:00 GMT)
2. $datefield is whatever datefield you have in your table
3. $comparison_date is your datefield in time in seconds since unix epoch
4. $datediff is number of seconds difference between datefields
5. $number_of_days_difference is your date difference divided by 86400 (which is the number of seconds in a day)
6. ceil rounds up (so you don't end up with 2.3234216321 days or some such number)

I have tested this code using about 10 different dates. So I'm not quite sure it's perfect. It returned the correct number for all my test scenarios involving correct dates. There is no error checking though, so it's kind of sloppy.

Anyway Toby, I hope this helps!


Cheers!
Laura

 

[danomac]

Maybe something like:

SELECT * FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(mydate) <= 5

Theoretically that should return records within 5 days of your date. Assumes, of course, mydate is defined in your table.

Oh I never tested that, but maybe that will help a bit.

 

[danomac]

Actually that query may return odd results.

This one should return records between 0 and 5 days:

SELECT * FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(mydate) BETWEEN 0 AND 5

Again, not tested. You may want to look into the TO_DAYS and BETWEEN functions of mysql.

 

[tobyheywood]

LTeeple,

Thanks for that snippet of code, I shall give it a go and let you know exactly how it goes, but at first glance it looks as though it will do the job nicely.

With regards to my initial query about the MySQL query, I was making a big, big mistake of not checking which version of MySQL I was using, as the DATEDIFF() is only available in ver 4.1+.

Danomac, thank you for your help, and your references to TO_DAYS and BETWEEN may prove exceptionally useful.

Thanks again.

Regards

Toby Heywood

 

[danomac]

tobyheywood: No problem.

Also, now that I've reread what I posted (I was pretty tired when I posted that) I noticed that you may have to reverse the NOW() and mydate to read:

TO_DAYS(mydate) - TO_DAYS(NOW())

as mydate is supposed to be in the future. That's what happens when you write queries when you are half-asleep.

 

[tobyheywood]

In the end I opted to use MySQL Server 4.1.7, live with the new mysqli_ functions as opposed to the good old mysql_ and it terms of additional functionality and time savers it seems the way forward.

Anyway my select csript now looks something like...

SELECT id,mem_no,reg_date,reg_renew,DATEDIFF(NOW(),reg_renew) as ddiff FROM members;

and my PHP code now contains an if statement which starts...

if($row['ddiff'] >= "-60") {
// then execute code here.
} else {
// do the normal code.
}

i have -60 to reflect the days between

Toby Heywood

 

[tobyheywood]

Thanks again to everyone for your help.

Kind regards



Toby Heywood