...

View Full Version : Date Diffs (the bane of my existence)



hinch
09-21-2009, 07:12 PM
I have a column in a mysql table that stored a mysql based NOW() date in the following format

2008-07-22 14:46:10

What I need to do is something like this against the field


if ($ffexists[5]<{3months prior to current date}) {
// do something
} else if (($ffexists[5]<{6months prior to current date}) && ($ffexists[1]==1)) {
//do something else
} else {
//do nothing
}

Its a little more complex in reality but that I can resolve my main sticking point is calculating the date 3 or 6 months from NOW() and converting it to a mysql format date and then doing the if comparison so if its 3 months and 1 second prior to now it flags it but if its still 1 minute before the 3 months ie: 2 months something it does nothing.

any help will be much appreciated

mlseim
09-21-2009, 09:05 PM
I don't know if this helps or not ....

The link below is about using a UNIX timestamp.
I like timestamp because the resolution is in seconds.
You can do math on it easily, down to the second.

Perhaps you can keep the existing date format and add another
column that represents it in timestamp form ... use that for calculations?

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Phil Jackson
09-21-2009, 10:22 PM
<?php
$monthAmount = 3; //how many months in the future
$now = time();
$time1 = strtotime("2008-07-22 14:46:10 + ".$monthAmount." months");

echo $now."<br />".$time1;

if($now >= $time1)
{
echo "arghhh!! ".$monthAmount." months+ have passed since data was inserted!!!! oh dear god!";
}
?>

hinch
09-22-2009, 12:46 AM
I don't know if this helps or not ....

The link below is about using a UNIX timestamp.
I like timestamp because the resolution is in seconds.
You can do math on it easily, down to the second.

Perhaps you can keep the existing date format and add another
column that represents it in timestamp form ... use that for calculations?

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

not an option unfortunately I already thought about that its on a legacy system with about 8 million records in the table so i'd have to run a process to convert all existing dates over to unix time to populate the new colum (legacy data needs to persist for FSA regulations :( )

hinch
09-22-2009, 12:47 AM
<?php
$monthAmount = 3; //how many months in the future
$now = time();
$time1 = strtotime("2008-07-22 14:46:10 + ".$monthAmount." months");

echo $now."<br />".$time1;

if($now >= $time1)
{
echo "arghhh!! ".$monthAmount." months+ have passed since data was inserted!!!! oh dear god!";
}
?>



that may work i'll have a bash at it in the morning see what I come up with.

hinch
09-22-2009, 10:32 AM
worked nicely thanks phil.

after making it fit the existing code base and fields etc I ended up with this.



$statussql = "SELECT LeadStatus FROM `tblleads` WHERE ID=".mysql_real_escape_string($_GET['cid']);
$statres = mysql_query($statussql);
if (!$statres) {
die('Invalid query: ' . mysql_error());
}
$statusid = mysql_fetch_row($statres);
if (($statusid[0]==5) || ($statusid[0]==6)) {
$monthAmount = 6; //how many months in the future
} else {
$monthAmount = 3; //how many months in the future
}

$now = time();
$time1 = strtotime($ffexists[5]." + ".$monthAmount." months");

//echo $now."<br />".$time1;

if($now >= $time1) {
$updatesql2 = "UPDATE `tblfactfind` SET govbonds=0,property=0,ftse=0,aim=0,spreadbetting=0,guarantee=0,statement=0 WHERE LID=".mysql_real_escape_string($_GET['cid']);
$updateres = mysql_query($updatesql2);
if (!$updateres) {
die('Invalid query: ' . mysql_error());
}
}

Phil Jackson
09-22-2009, 10:41 AM
no worries glad it worked :-)

hinch
09-22-2009, 10:47 AM
no worries glad it worked :-)

yeah saved me considerably white hair and bald patches.

if there's 2 things i've never been able to get the hang of in php since switching from primarily c# its dates/times/timestamps and regex's everything else has been a fairly straight forward switch over but those 2 things are just killing me.

Phil Jackson
09-22-2009, 10:54 AM
man, wish id started with C#. Look at it once then went to bed and that was that! creating classes is my downfall. It was regex but now i kinda like it!

P.S. You can never get your head around PHP, there's a function for everything! i find a new one everyday!

hinch
09-22-2009, 01:42 PM
not just a function for everything but in the case of regex's and str functions in particular there's 3-4 in most cases that could all be used to achieve the same thing.

The one thing I've yet to find a real use for though in php is OO based design. I'm still yet to see any real need for it over just well written reusuable functions all it seems to do is add overhead to the page load since its interperated and not compiled I just can't see any benefit for them.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum