PHP: Calculate Real Differences Between Two Dates or Timestamps - Comment Page: 6
I was using simple function to calculate difference between two dates and timestamps until I noticed, it's not working correctly in long intervals. It's very easy to calculate difference between two timestamps in seconds, but it's much more complicated print difference in human readable format. The Internet can be found in a wide range of ways to do this thing, but as a rule they use a fixed amount of seconds for the year and the month. So if we calculate year with using 365 or 365.25 days and month using 30 or 31 then the difference is not accurate, because of leap years, DST (Daylight Saving Time) and so on.
Because of this problem, I decided to make a function (at least in the short...
Please excuse my typo in the first code listing. It should read:
echo time() . " - " . $row[logonDateTime] . "
";
echo "difference is " . (time() - $row['logonDateTime']) . "
";
echo time() . " - " . $row[logoffDateTime] . "
";
echo "difference is " . (time() - $row['logoffDateTime']) . "
";
The only change was the reference to the database item $row[logoffDateTime] on the third line. It had no impact on the output other than to make it a little confusing to read where the data was outputting from. The output above is still accurate as the piece of code that calculated it was correct, just not its label.
Solved
Ok perhaps you have a better solution than what I came up with. The issue came from your first test of is_int(). In my situation where I am being returned a timestamp created by mysql from a datetime filed, php was treating the timestamp as a string. I do not know why it would default to treating a “string” of numbers as a literal string as aposed to one number but i used the following hack to make it work. Do you know of a better way to typecast with a language that has no casting?
$lastLogon = dateDiff(time(), ($row[logonDateTime] + 0));
echo $lastLogon . "
";
$lastLogoff = dateDiff(time(), ($row[logoffDateTime] + 0));
echo $lastLogoff . "
";
Notice that I have added 0 to the returned timestamp.
Any suggestions?
Better Solution
$lastLogon = dateDiff(time(), (int)$row[logonDateTime]);
echo $lastLogon . "
";
$lastLogoff = dateDiff(time(), (int)$row[logoffDateTime]);
echo $lastLogoff . "
";
Ok so its official, i have been behind this screen for way too long. Thank you again for your contributions.
Hi Wes,
You got this totally solved? Or do you need some help with this still?
Thank for the function!
I’ve read many dateDiff functions on the internet which don’t contemplate the years problem.
Santiago
Very nice post. I tried writing something myself for my site, but no luck, so I Googled. Your site was 3rd listed and I’m so happy I found it. Bookmarked and waiting for more.
This function makes my life so easy! :) *applauds*
Hi JR
Will you help me also plzzzzzz
My prob is i cant find the diff when the total hours exceeded above 24
that is
Assigned Hours is 30:00:00
Completed hours is 20:00:00
i want to find the difference between these hours
that is
Remaining Hours as 10:00:00
will you please help me as soon as possible
Hi Keerthi,
So you are using only hours, minutes and seconds without days, months and years?
yes…..
if assigned hours or completed hours gets greater than 24:00:00 ,then i’m not able to find the difference.wht will i do to find the difference.
In one of the above codes it shows how to find out the total no of working hours.
After that how can i find the diff between assigned and completed hours??
Plz help me
Hi again Keerthi,
I think best way is simply convert hours, minutes and seconds to seconds and then calculate difference and then convert seconds back to hours, minutes and seconds. I wrote following code to do this:
Function usage:
Output:
This code is only quick example without any input value checks and I assume that the assigned time is always equal or greater than completed time. So this is maybe not production ready code, but I think you get the idea… :)
Thank alot….
it works
thank you so much
You’re welcome! ;)
how can i set a reminder for my insurance policies that reminds me 7 days before the due date of premium. one is yearly and another is monthly. Policies were bought in june 2001(yr) and oct 2007(monthly).
$trime1 = time();
$trime2 = "(".$get_info['dt'].")";
$trime3 = "(".$get_info['lastchange'].")";
if ($get_info['status'] == 3){
$diff = "\t".dateDiff($trime2, $trime3)."\n";
echo $diff;}
else {
$diff = "\t".dateDiff($trime2, $trime1)."\n";
echo $diff;}
Thanks for this function, been scourging around the net for it. But i was having an issue. As you can see on the code above, i’m trying to do a datediff on a row list directly from my SQL database. dt column data type is datetime while lastchange column is timestamp. Result from doing the code above is : 41 years, 10 months, 13 days, 4 hours, 2 minutes, 52 seconds. Since i’m practically not too familiar with php code, may i know whether is there a way for me to tackle this issue? Thanks!
Hi BlackHowling,
Could you also post exact values (MySQL datetime and timestamp), what you get from MySQL database?
Looks some problem with datetime data type, but if you could “echo” those values and post here…
Will do.
These are 3 example values from DT column (datetime data type) based on my original SQL query made for the project that i’m currently doing (a total of 100 rows).
A1 2011-11-14 14:57:32
A2 2011-11-14 14:36:05
A3 2011-11-14 14:13:21
and these would be the lastchange which is in timestamp datatype,
B1 2011-11-14 15:11:10
B2 2011-11-14 15:12:10
B3 2011-11-14 14:13:21
Basically im trying to pull of an aging column, which your function will work as the function to calculate the aging, for example, B1 – A1, B2 – A2 and lastly B3 – A1. So it will print out the aging period of each row A1, A2, A3… etc. Seeing the format is basically the same in the data, it shouldn’t be an issue right? I’ve also tried Wes’s method, but failed. :(
If I try run dateDiff function with your values:
I get following output:
Note the last one is empty, because there is no difference…
So I’m not sure anymore, what is the problem? :)
Thank you for your time, by the way. Really appreciated. Anyway, the issue lies at it doesn’t want to calculate my SQL queries unless i put up the values inside of it which i can’t because the data was updated every half an hour. Therefor, i was trying to make the function to auto calculate the column and print out a result on a new row for it, perpendicular to the data that was listed. Let me give you the link of a test page i made. http://support2.netmyne.com/testing.php (while it’s still there). As you can see, instead of publishing it in a new row, it decides to give up and print the Aging (the one where i used the function) down below, calculating god knows what.
Thanks! Now I got it, what you are doing… :)
But if you want print that aging every rows then, I think it is not big problem…just run dateDiff function once per every row with your timestamps?
Thanks. Let me try a simple php method based on that logic.
Let’s say the table “test” contains dt(dateTime), lastchange(timestamp), and status(to check for open/resolved).
Example,
“SELECT dt,lastchange,status FROM test ORDER BY dt DESC LIMIT 100” would be the SQL query to get the latest data sorted by the latest data accordingly to the dt column,
Full code would be;
$time2) {
$ttime = $time1;
$time1 = $time2;
$time2 = $ttime;
}
// Set up intervals and diffs arrays
$intervals = array('year','month','day','hour','minute','second');
$diffs = array();
// Loop thru all intervals
foreach ($intervals as $interval) {
// Set default diff to 0
$diffs[$interval] = 0;
// Create temp time from time1 and interval
$ttime = strtotime("+1 " . $interval, $time1);
// Loop until temp time is smaller than time2
while ($time2 >= $ttime) {
$time1 = $ttime;
$diffs[$interval]++;
// Create new temp time from time1 and interval
$ttime = strtotime("+1 " . $interval, $time1);
}
}
$count = 0;
$times = array();
// Loop thru all diffs
foreach ($diffs as $interval => $value) {
// Break if we have needed precission
if ($count >= $precision) {
break;
}
// Add value and interval
// if value is bigger than 0
if ($value > 0) {
// Add s if value is not 1
if ($value != 1) {
$interval .= "s";
}
// Add value and interval to times array
$times[] = $value . " " . $interval;
$count++;
}
}
// Return string with times
return implode(", ", $times);
}
$result = mysql_query("SELECT dt,lastchange,status FROM test ORDER BY dt DESC LIMIT 100");
echo "
Date Receive Rating
Last Change
Aging
";
while($row = mysql_fetch_array($result))
{
echo "";
echo "" . $row['dt'] . "";
echo "" . $row['lastchange'] . "";
$trime1 = time();
$trime2 = "(".$row['dt'].")";
$trime3 = "(".$row['lastchange'].")";
if ($get_info['status'] == 3){
$diff = "\t".dateDiff($trime2, $trime3)."\n";
echo $diff;}
else {
$diff = "\t".dateDiff($trime2, $trime1)."\n";
echo $diff;}
echo "";
}
echo "";
mysql_close($con);
?>
http://support2.netmyne.com/testing.php still getting the same result :( /chugs a beer. i just don’t understand why it total out the aging, and print it on every single row, lol.
Thanks! Now I finally see where the problem is… :)
The problem is brackets. Let’s see…following:
Should be simply:
Do you get it working now? :D
That’s it! OH MY GOD! SUCH SIMPLE THING ! D: Thank you very much, good sir! In the end it was my coding’s fault! I apologized! D: Thanks! /toss a beer
New Result :
2011-11-15 14:21:02 2011-11-15 14:23:13 2 hours, 23 seconds
2011-11-15 13:55:30 2011-11-15 14:17:05 2 hours, 25 minutes, 55 seconds
2011-11-15 12:13:43 2011-11-15 13:24:59 4 hours, 7 minutes, 42 seconds
You are welcome! Yes, very simple thing, but hard to find… :D Funniest thing is that you posted it on your first post… :)
And no problems! :) It’s most important that you got it working as it should be! ;D
hello. wonderful script.
I am trying to find the difference between a given date, that can be from the past or from the future, and the current date. But the current script only says the difference. It does not say for example 14 days ago, or 14 days left. How can I do that? How can find a non-absolute difference in days only. The output can be in negative if the given date is from the past and positive if the given date is from the future. I am trying to find the difference in days so i dont need all the year, min, seconds, month variables. Thanks
Abu
Hi Abu,
Thanks!
This is totally new topic and off topic, so could you please post your question to our forums -> PHP Board let’s see what we could do. It’s better place to discuss anyways. :)
@JR.
ok. I did.
Thanks.
Abu
Thanks Abu! :) I will check your problem soon…
Why not use the native functionality? The format function in the DateTime class can be used to output the same information with just a few lines of code.
$now = new DateTime('now');
$then = new DateTime($date);
$diff = $now->diff($then);
echo $diff->format('%a days');
Hi Charlie,
Thanks, I actually post this style solution here and also native MySQL example. ;)
I love you code JR it’s really functional but is there a way I can get the difference ONLY in one of the time.
For example, i want the following:
difference between 2011-12-14 21:54 and 2011-12-16 20:45
— if i need it in HOURS then it will give for example 22.8 Hours
— if i need it in MINUTES for examples it will return 1368
and so on??
if we were able to to this it will HELP me alot.
THANKS AGAIN
Sevag
Hi Sevag,
What intervals you want to use? Seconds, minutes, hours, days…?
Years
Month
Week
Day
Hour
Minutes
I need this for the following reason
If my client asked for service every 10 minutes between 2011-12-16 10:00 till 2011-12-17 10:25
Since he picked minutes as interval then i want something that gives me in this case 1465.
Then i will do simply 1465 / (10) = 14.65. So this means 14 services.
Now if he were same time interval but he picked every 2 hours
Then the formula should give me 24.2
Then i do 24.2 / 2 = 12.1 . so this means 12 services.
Sorry 1465/10 = 146.5 => 146 services
Hi again Sevag,
Do you need output example as 24.2 hours or is just 24 hours integer value enough? This full double/floating point value with decimals is easy to get with minute, hour, day, week intervals, but it’s very hard to get with month and year intervals, because we can’t use fixed seconds for month and year. If you want to use this date diff function logic and accuracy, then it’s only possible to get integer values.
just the integer value is enough
but supposing minutes interval
instead of getting
1 day 3 minutes
I get
1443 minutes
Okey :) Then check following code:
Output:
Note. first examples use your timestamps and last examples use date 2014-06-17… :)
I just tested this quickly and seems to be working, please let me know, if you have any problems with that?
First of all thank you for your code, it helped me alot. But i just realized something that got me puzzled.
Suppose I have the following two time-stamps:
$start =’2011-12-18 23:19:00′;
$end =’2012-02-08 13:20:00′;
$interval=3;
I tried to compute the difference in hours using two different approaches and I got a small difference.
1.
$howmany=floor($difference/($interval*60*60)+1);
output was 413
2.$howmany=(dateDiff( $start , $end , ‘hour’)/$interval)+1;
out was 414
I tried the same two approaches for minutes, days, and weeks and answers were IDENTICAL. but just never figured out why in this specific case answers weren’t the same. Any suggestions?
Thank you,
Sevag
Actually, I can’t see the problem here, so let’s look at this in more detail…
First if we compare just fixed value approach, with dateDiff:
Output:
So this looks good, exactly same, but dateDiff output without decimals, as it should be.
Then divide results with three (your interval):
1238.0166666667 / 3 = 412.672222222
1238 / 3 = 412.666666667
Then add one (I don’t know why, but if you just have to add one):
412.672222222 + 1 = 413.672222222
412.666666667 + 1 = 413.666666667
Then you use floor to round first value fractions down:
floor(413.672222222) = 413
413.666666667
So actually second value is 413.666666667 at this point, not 414…and first value fractions is rounded down and it’s 413…
you are great , your code and function is very useful
Thanks to help all