Ask Ben: Displaying And Formatting The Difference Between Two Dates

Posted August 13, 2008 at 9:04 AM

Tags: ColdFusion, Ask Ben

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.

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment