Ask Ben: Displaying And Formatting The Difference Between Two Dates
Sir, i want to know the solution for which iam struggling for some days... that is how we have to caluculate difference between two dates such that it have to show the difference in the format yyyy (years):m (months): ww (weaks):w (weakdays) : h (hours): n (minutes) : s (seconds). Sir, to confirm exactly what my doubt mean is...if we go for ebay ..and if we find there the products displaying and the estimated time left to buy... there they show the time left...like that i want if i gve two dates it have to show the exact difference between them......as shown in above format..
thanking you sir.....for providing the oppurtunity and iam waiting for your reply kindly.......
This task is actually much less complicated than it might sound. ColdFusion is quite excellent at both handling and formatting dates. To solve this problem, at least mostly, all we need to is the following:
- Find the difference between your two dates (the current time and the target time).
- Format that difference in the desired way.
As you may have read before, ColdFusion's date math makes finding the difference between two dates simple - all we need to do is treat the dates as numbers and do some subtraction:
<!--- Get a start date for our comparison. --->
<cfset dtFrom = ParseDateTime( "01/01/2008 12:00:00 AM" ) />
<!--- Get the current date. --->
<cfset dtTo = Now() />
<!---
Get the difference between these two dates. Using
ColdFusion Date Math will give us the numeric version
of the timespan that represents the difference between
these two dates... this timespan is represented in the
number of days between the two dates.
--->
<cfset dtDiff = (dtTo - dtFrom) />
<!---
Now that we have the difference between the two dates, we
simply need to format that date/time span using ColdFusion's
built-in formatting functions.
--->
#DateDiff( "yyyy", "12/30/1899", dtDiff )# Years,
#DateFormat( dtDiff, "m" )# Months,
#DateFormat( dtDiff, "d" )# Days,
#TimeFormat( dtDiff, "h" )# Hours,
#TimeFormat( dtDiff, "m" )# Minutes,
#TimeFormat( dtDiff, "s" )# Seconds
When outputting the difference, you have to be careful of the year. That is one tricky thing in ColdFusion. Dates don't start at zero - they start at 12/30/1899. Therefore, when we get our time span, we have to get the difference in years between our time span value and the "start" date of ColdFusion dates. The rest of the formatting should be straightforward.
Running the above code, we get the following output:
0 Years, 8 Months, 12 Days, 9 Hours, 2 Minutes, 42 Seconds
I hope this puts you on the right track.
Want to use code from this post? Check out the license.
Reader Comments
Ben,
I couldn't resist taking this a little further http://www.stephenwithington.com/blog/index.cfm/2008/8/15/Using-ColdFusion-to-Create-an-Ebayesque-Auction-Countdown-Timer-Custom-Tag
@Steve,
That's some slick stuff.
When I use this functionality for "d", "h" and "m" on a date from an MSSQL database (should it matter?)
<cfset dtFrom = sel_last_call.eventdate/>
<cfset dtTo = Now() />
<cfset dtDiff = (dtTo - dtFrom) />
#DateFormat( dtDiff, "d" )# Day<cfif DateFormat( dtDiff, "d" ) neq 1>s</cfif>,
#TimeFormat( dtDiff, "h" )# Hour<cfif TimeFormat( dtDiff, "h" ) neq 1>s</cfif>,
#TimeFormat( dtDiff, "m" )# Minute<cfif TimeFormat( dtDiff, "m" ) neq 1>s</cfif>
I get some odd results
The difference between
6/23/2009 4:40:00 PM and Now (6/26/09 2:15 pm)
1 Day 9 Hours 35 Minutes
6/24/2009 12:00:00 PM and Now (6/26/09 2:15 pm)
1 Day 2 Hours 15 Minutes
@Charles,
First off, where the date comes from (MS SQL, MySQL, ACCESS, etc.) is not relevant. The issue you are having is not apparent right away - definitely not intuitive unless you work with date math a lot.
See, when you subtract one date from another using mathematical operators, you create a numeric date value. In the first example you gave, FROM - TO, results in:
2.89930555555
This value is a numeric representation of the time span between your two dates.
Now, the way we numeric dates work is that they are considered the number of days since the ZERO date in ColdFusion, which you can see if you do this:
#DateFormat( 0, "full" )#
... which gives us:
Saturday, December 30, 1899
And so, your mathematical date value, 2.8993...., is consider to be 2.8993.... days AFTER 12-30-1899.
So then, the next thing you are asking it is to get the date of that date:
DateFormat( dtDiff, "d" )
This is giving you "1". This is because if you add 2.899 days to 12-30-1899, you get 1-1-1900. And, the "day" of that is "1".
What you want is NOT the DateFormat() of the time span, but rather the DAYS of the time span, which is:
#Fix( dtDiff )#
Fix() in ColdFusion truncates a number (removes decimal), leaving you with just the integer, which is the number of dates in the time span.
The next issue is that we are dealing with 24 hours, but you are using the small "h", which is 12 hours. So, it gives you 9 hours, but really, its thinking 24 hours time, so its 21.
All together, you need to do this:
<cfset dtFrom = ParseDateTime( "6/23/2009 4:40:00 PM" ) />
<cfset dtTo = ParseDateTime( "6/26/2009 2:15:00 PM" ) />
<cfset dtDiff = (dtTo - dtFrom) />
#Fix( dtDiff )# day(s)
#TimeFormat( dtDiff, "H" )# hour(s)
#TimeFormat( dtDiff, "m" )# miunutes(s)
... which results in :
2 day(s) 21 hour(s) 35 miunutes(s)
I hope that helps clear it up a bit.
Ahh... got it. Thanks mucho.
Ug....
Ben, thanks man for this post. Date math messes with my head. I was even struggling after I read this because I was skipping steps. I actually had to write out each operation I was doing so I could figure out where I went wrong.
[Now, in my defense I programmed for a solid 14 hours yesterday, but nonethless..]
I wanted to log the ignore the user session for a period of time if they tried too many times to login.
Here's the code that did made the word problem:
<cfset dtFrom = ParseDateTime(session.ignored) />
<cfset dtTo = ParseDateTime(now()) />
<cfset dtDiff = (dtTo - dtFrom) />
<cfset daysAgo = Fix(dtDiff) />
<cfset hoursAgo = TimeFormat( dtDiff, "H" ) />
<cfset minutesAgo = TimeFormat( dtDiff, "H" ) />
<cfset secondsAgo = TimeFormat( dtDiff, "H" ) />
<cfset totalSecondsAgo = (daysAgo * 86400) + (hoursAgo * 3600) + (minutesAgo * 60) + secondsAgo />
<cfset sessionIgnoreSeconds = APPLICATION.ignoreLoginMinutes * 60 />
<cfset ignoreSecondsRemaining = sessionIgnoreSeconds - totalSecondsAgo />
<cfoutput>
Thanks to Ben, i know the session was ignored
#daysAgo# day(s)
#hoursAgo# hour(s)
#minutesAgo# minute(s)
#secondsAgo# second(s) ago.<br />
The application requires an ignore period of #APPLICATION.ignoreLoginMinutes# minute(s)<br />
#APPLICATION.ignoreLoginMinutes# minute(s) is #sessionIgnoreSeconds# seconds(s)<br />
The ignore period has lasted #totalSecondsAgo# second(s)<br />
#sessionIgnoreSeconds# second(s) minus #totalSecondsAgo# second(s)
is #ignoreSecondsRemaining# second(s).<br />
If #ignoreSecondsRemaining# is less than or equal to 0 second(s), the ignore period has expired.<br />
</cfoutput>
Here's the output of the code that I wrote, showing my work [images of math class dance in my head]:
Thanks to Ben, i know the session was ignored 0 day(s) 1 hour(s) 1 minute(s) 1 second(s) ago.
The application requires an ignore period of 2 minute(s)
2 minute(s) is 120 seconds(s)
The ignore period has lasted 3661 second(s)
120 second(s) minus 3661 second(s) is -3541 second(s).
If -3541 is less than or equal to 0 second(s), the ignore period has expired.
Thanks Ben. Date math is the bane of my existence.
/me smacks forehead
got it wonky a bit in the code above... code should be:
<cfset dtFrom = ParseDateTime(session.ignored) />
<cfset dtTo = ParseDateTime(now()) />
<cfset dtDiff = (dtTo - dtFrom) />
<cfset daysAgo = Fix(dtDiff) />
<cfset hoursAgo = TimeFormat( dtDiff, "H" ) />
<cfset minutesAgo = TimeFormat( dtDiff, "m" ) />
<cfset secondsAgo = TimeFormat( dtDiff, "s" ) />
<cfset totalSecondsAgo = (daysAgo * 86400) + (hoursAgo * 3600) + (minutesAgo * 60) + secondsAgo />
<cfset sessionIgnoreSeconds = APPLICATION.ignoreLoginMinutes * 60 />
<cfset ignoreSecondsRemaining = sessionIgnoreSeconds - totalSecondsAgo />
<cfoutput>
Thanks to Ben, i know the session was ignored
#daysAgo# day(s)
#hoursAgo# hour(s)
#minutesAgo# minute(s)
#secondsAgo# second(s) ago.<br />
The application requires an ignore period of #APPLICATION.ignoreLoginMinutes# minute(s)<br />
#APPLICATION.ignoreLoginMinutes# minute(s) is #sessionIgnoreSeconds# seconds(s)<br />
The ignore period has lasted #totalSecondsAgo# second(s)<br />
#sessionIgnoreSeconds# second(s) minus #totalSecondsAgo# second(s)
is #ignoreSecondsRemaining# second(s).<br />
If #ignoreSecondsRemaining# is less than or equal to 0 second(s), the ignore period has expired.<br />
</cfoutput>
/me hates date math
@Grant,
Date math is extremely powerful and can be very frustrating :) Looks like you are getting the hang of it. If I can give you two points of feedback: the now() method already returns a date/time object, so there is no need to parse it. Also, one function that might make your life a little easier is the dateDiff() method. I'm not saying you need it here, but sometimes it is easier than wrappin your head around the date math.
Good stuff!
@Ben Nadel,
Thanks. DateDiff() is one of those functions that I'm aware of, but how it really works is a bit foggy to me. I've used it before, but I don't use it much. This could be because it confuses me, or because I haven't needed it. I just checked adobe's documentation and they note something interesting in the change history for CF8:
- Changed how negative date differences are calculated: this function calculates negative date differences correctly; its output may be different from that in earlier releases.
- Changed the w and ww masks; they determine the number of full weeks between the two dates.
Since I'm stuck in CF7 [company has old software], this must be why I don't use DateDiff(). :)
Cheers!
@Grant,
Hmm, funky. I haven't used it a ton; seems like it used to be a bit buggy!
Hi Ben,
I was trying out your code and getting the wrong results. Then I realise your code (I think) doesn't really work...
CF represents datetime as a real no where the integer part represents the no of days before/after a point 0 = 12/30/1899, the fractional part is the time. So taking a difference merely shifts it from your date (e.g. 2008/01/01) to around 12/30/1899, which still produces the incorrect value under some circumstances:
- Say Date1=Date2 -- this means diff =0 , and the month=12, day=30 which is incorrect.
- Doesn't take care of leap years properly since you are transplanting everything to 1899.
- Why did your example worked? Because 12/30 is close enough to your example reference, 01/01 to make it sort of work, when it didn't actually.
At best, we can write one that is up to the granularity of day:
#Fix(dtDiff)# Days,
#TimeFormat( dtDiff, "h" )# Hours,
#TimeFormat( dtDiff, "m" )# Minutes,
#TimeFormat( dtDiff, "s" )# Seconds
I post this here so that others would not end up barking up the wrong tree as I did!
Regards,
Andrew
@Andrew,
Ahh, you are absolutely right about the problems with this approach. Thank you for pointing this out. I guess sometimes stuff just works "accidentally" - like a broken clock twice a day.
I am not sure I understand the issue with leap years though. When taking into account the amount of time between two days, as long as numeric representation of the dates is correct, I think the leap year shouldn't affect it. For example, when I run this:
<cfset a = "2007/02/28" />
<cfset b = "2008/02/28" />
<cfset c = "2009/02/28" />
#(b - a)#<br />
#(c - b)#<br />
I have one statement crossing a regular year and one crossing a leap year. When I run this, I get:
365
366
The second statement took into account the extra leap year day.
I think maybe I am just missing what you are saying about the leap year.
Hi Ben,
Sorry I was a bit garbled when I said that, and the example I gave are a bit of a redundancy!
As long as we use your method to figure out the difference in days, hours, mins and seconds -- it still works perfectly under all circumstances. So your example works, since you are working with days.
What I was pointing out is that when we use the method to try to figure out the month or year difference, we will definitely run into problems.
Lets leave out month because it is always arguable and confusing what constitute 'crossing' a month. I'll point out what I meant by year problem being affected by leap year.
<!--- 2007 is not a leap year --->
<cfset dtFrom = ParseDateTime( "01/01/2007" ) />
<cfset dtTO = ParseDateTime( "12/31/2007" ) />
<cfset dtDiff = (dtTo - dtFrom) />
#DateDiff( "yyyy", "12/30/1899", dtDiff )# Years
<!--- This gives the result 0 years which is correct --->
<!--- 2008 is a leap year --->
<cfset dtFrom = ParseDateTime( "01/01/2008" ) />
<cfset dtTO = ParseDateTime( "12/31/2008" ) />
<cfset dtDiff = (dtTo - dtFrom) />
#DateDiff( "yyyy", "12/30/1899", dtDiff )# Years
<!--- This gives the result 1 years which is wrong --->
The reason is that we are shifting the years to the 12/30/1899-12/30/1900 period and using the year function to detect year changes, which is not a leap year. So when we transplant a leap year into 1899-1900 which is not a leap year, we get incorrect results.
Hope this makes it clearer. However, your method still works fine up to the day level.
Some correction to the Hours to what I posted earlier (need to user 24 hour format). The one below works under all circumstances I think:
<cfset dtDiff = (dtTo - dtFrom) />
#Fix(dtDiff)# Days,
#TimeFormat( dtDiff, "H" )# Hours,
#TimeFormat( dtDiff, "m" )# Minutes,
#TimeFormat( dtDiff, "s" )# Seconds
Cheers,
Andrew
@Andrew,
I think I see what you're saying now. To be honest, thinking in terms of date-spans is not an easy task for me. I can think in terms of days and hours since those are not bound by any external concepts; but, once you get into months and years, I find the conceptual picture much more foggy. Not sure why :) But, I think I see what you're saying.
Dear Ben
Looks great this, but I am getting a strange result. I want to calculate a difference which should be several seconds, like thirty. My result is this:
{ts '2011-10-06 11:03:30'}
{ts '2011-10-06 11:03:00'}
0 Years, 12 Months, 30 Days, 0 Hours, 0 Minutes, 30 Seconds
The years are correct.
The Hours, minutes and seconds are correct.
But what's with the months and days?
This is how I do the math:
nows = createDateTime(2011,10,6,11,3,0);
futures = createDateTime(2011,10,6,11,3,30);
parseFuture = parseDateTime(futures);
parseNow = parseDateTime(nows);
dtDiff = (parseFuture - parseNow);
#DateDiff( "yyyy", "12/30/1899", dtDiff )# Years,
#DateFormat( dtDiff, "m" )# Months,
#DateFormat( dtDiff, "d" )# Days,
#TimeFormat( dtDiff, "H" )# Hours,
#TimeFormat( dtDiff, "m" )# Minutes,
#TimeFormat( dtDiff, "s" )# Seconds
Would really appreciate it if you could help me, please.
Thanks alot!
Maarten
At cursory glance (and unrelated to your months/days issue) I believe your minutes and hours may not be correct. Minutes requires 'n', not 'm' and the hours show as 'H' when it should be 'h'.
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_c-d_28.html
Scratch my previous comment. I got TimeFormat and DateDiff parameters mixed up. Too early to be helping people.
Please see my rather convoluted explanation above on why it doesn't work for months and years :)
This method only works for: day (using fix), hours, mins, secs. It's borked for months, years. Basically it shifting everything (taking the difference) to 30/12/1899 as the zero-point, which is why the Day=30 and Month=12.
What definitely works is below:
<cfset dtDiff = (dtTo - dtFrom) />
#Fix(dtDiff)# Days,
#TimeFormat( dtDiff, "H" )# Hours,
#TimeFormat( dtDiff, "m" )# Minutes,
#TimeFormat( dtDiff, "s" )# Seconds
@Andrew,
Thanks for your reply. Too bad it does not work that easily. Luckily after some thinking, I came to the conclusion that I actually only needed the seconds. Currently I do not have the code with me. Right now it's @ work and am too tired to log in remotely.
Thanks,
Bye!!
This isn't adding correct for me. I'm using this:
<cfset dtDiff = (qsearch.ReceiptDate - qsearch.LAST_SOLD) />
#DateDiff( "yyyy", "12/30/1899", dtDiff )# Years,
#DateFormat( dtDiff, "m" )# Months,
#DateFormat( dtDiff, "d" )# Days
data is 10/22/10-03/29/11 I get his 0 Years, 7 Months, 25 Days. Which is wrong should be 5 months, 7 days. So where is it going wrong?
The code doesn't work, try this small testcase:
You will get:
Which is clearly wrong.
Old post but thanks.
If anyone comes across this and is looking for something that works (no offence Ben but your code didn't as explained in the comments) then please look here:
http://stackoverflow.com/questions/24842248/date-time-enhancement-for-custom-function/