A reader sent in an interesting issue. He was using createTimeSpan to list out 5 minute intervals between two hours. Here is what he used:
<cfset dtHour = CreateTimeSpan(
0, <!--- Days. --->
0, <!---
Hours. --->
5, <!--- Minutes. --->
0 <!--- Seconds. --->
) />
<cfset thecount = 0>
<cfloop from="9:00 AM" to="10:00 AM" step="#dtHour#" index="i">
<cfset thecount+=1>
<cfoutput>#timeformat(i,"short")#<br /></cfoutput>
</cfloop>
<cfoutput>
#thecount#
</cfoutput>
In the code above he creates a 5 minute time span and then loops from 9 AM to 10 AM. I rarely use createTimeSpan, and when I have used it, I only used it with query caching. This worked... until he added another loop:
<cfset thecount2 = 0>
<cfloop from="10:00 AM" to="11:00 AM" step="#dtHour#" index="i">
<cfset thecount2+=1>
<cfoutput>#timeformat(i,"short")# #i#<br /></cfoutput>
</cfloop>
<cfoutput>
#thecount2#
</cfoutput>
This should show the same results, just a different hour, right? Check out the results:
What the heck? (Actually when I ran this I said something a bit strong.) There are 13 counts in the first loop and 12 in the second. Also note the second stops at 10:55, not 11.
I was a bit lost at first, but then I remembered something. The interval value is actually a number. On a whim I modified the code to output the interval and the index within the loop:
<cfset dtHour = CreateTimeSpan(
0, <!--- Days. --->
0, <!---
Hours. --->
5, <!--- Minutes. --->
0 <!--- Seconds. --->
) />
<cfoutput>#dtHour#<p></cfoutput>
<cfset thecount = 0>
<cfloop from="9:00 AM" to="10:00 AM" step="#dtHour#" index="i">
<cfset thecount+=1>
<cfoutput>#timeformat(i,"short")# #i#<br /></cfoutput>
</cfloop>
<cfoutput>
#thecount#
</cfoutput>
<br /><br />
<cfset thecount2 = 0>
<cfloop from="10:00 AM" to="11:00 AM" step="#dtHour#" index="i">
<cfset thecount2+=1>
<cfoutput>#timeformat(i,"short")# #i#<br /></cfoutput>
</cfloop>
<cfoutput>
#thecount2#
</cfoutput>
Check out the result:
Ah, floating point numbers. I think you can see here where the issue is coming up - rounding errors. Notice how even the values for 10AM (at the end of the first loop and the first entry of the second loop) don't match.
Nice, so now we know why, how can we rewrite this? Here is a slightly modified version using a conditional loop:
<!--- number of minutes --->
<cfset step = 5>
<cfset theTime = "9:00 AM">
<cfset toTime = "10:00 AM">
<cfloop condition="dateCompare(theTime,toTime) lt 1">
<cfoutput>thetime=#timeFormat(thetime)#<br></cfoutput>
<cfset theTime = dateAdd("n", step, theTime)>
</cfloop>
<p/>
<cfset theTime = "10:00 AM">
<cfset toTime = "11:00 AM">
<cfloop condition="dateCompare(theTime,toTime) lt 1">
<cfoutput>thetime=#timeFormat(thetime)#<br></cfoutput>
<cfset theTime = dateAdd("n", step, theTime)>
</cfloop>
This uses a simple numeric value for the number and passes it to the dateAdd function. What's kind of cool about this code is that you could also do non-even steps as well. (Sorry, not even as an even/odd, but a step value that won't fit evenly into the interval.)
Archived Comments
Ray,
Very interesting stuff! I wanted to do a little more digging and it seems as if the problem lies in how ColdFusion converts date/time stamps into numeric date representations. An alternate solution would be to define the From and To attributes using CreateTimeSpan() as well:
http://www.bennadel.com/blo...
This makes me think it's not a rounding issue, per say, otherwise, the same thing should have happend. It seems that the rounding issue is when CF converts a date/time string to a date/time number.
Amazing, I had no idea that you could use values such as "9:00 am, 10:00am, etc.." in a <cfloop>. I probably would have tackled the problem by running a dateDiff() in mins between the two times to set my start and end attributes. I think the readers approach is more elegant though.
I didn't know you could use times in loops like this either! Very cool. Ben, I like your solution of using CreateTimeSpan.
Thanks for the extremely fast response to my question Ray! Thanks to Ben for showing me how to loop over times in the first place: http://www.bennadel.com/blo...
Just remember that no matter what you do you cannot change the future - just ask the people on LOST!
I ran into a similar issue yesterday when using a from/to cfloop.
From the cfloop livedoc page:
Usage
Using anything other than integer values in the from and to attributes of an index loop can product unexpected results. For example, if you increment through an index loop from 1 to 2, with a step of 0.1, ColdFusion outputs "1,1.1,1.2,...,1.9", but not "2". This is a programming language problem regarding the internal representation of floating point numbers.
Note: The to value is evaluated once, when the cfloop tag is encountered. Any change to this value within the loop block, or within the expression that evaluates to this value, does not affect the number of times the loop is executed.
It's even more interesting since Java (and SQL, .NET, etc) stores Date/Time values as the number of milliseconds since epoch/midnight. You would think that it would then follow that CF TimeSpans would just be a number of milliseconds. So 10AM would just be 10 * 60 *60 * 1000 milliseconds since midnight. I'm curious why they would choose to use a float instead.
@Roland,
I am not so sure that SQL uses milliseconds. I believe it treats numeric dates as floats as well (where int is the day and decimal is the time). That's why Rounding a numeric date/time stamp in SQL will result in a date-only value (no time).
I do think, however, that the zero dates in SQL and ColdFusion are slightly different. Meaning, "0" in CF is a different date than "0" in SQL.
Well at least MSSQL stores 8 byte datetimes. The first 4 bytes are milliseconds since your start date (1/1/1900 most of the time), the second four bytes are the number of 1/300ths of a second since midnight. It doesn't technicaly use a float though - it uses a counter of 1/300ths of a second. Because of this there is a specialized rouding algorigthm based on the fractional seconds that are calculated by any datetime operations.
http://msdn.microsoft.com/e...
The behavior is *almost* float like, but not quite. And it's definitely annoying :)
@Roland,
Maybe I am confused; it appears the documentation says the first 4 bytes are *not* milliseconds, but, rather days:
"The first 4 bytes store the number of days before or after the base date: January 1, 1900."
You're right - I'm multitasking too much today. I meant to say days, fractional seconds :)
@Roland,
No worries. One thing we have to be careful of is that the zero dates in CF and SQL *are* different. So, if you use numeric dates, you can't transfer them across the bridge.
Get some rest this weekend. (I plan to see many movies!)
@JeffS: Interesting. I didn't know the docs explicitly said not to use anything but integers. That's an important note.