Fascinating issue with createTimeSpan
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:
2 0, <!--- Days. --->
3 0, <!---
4Hours. --->
5 5, <!--- Minutes. --->
6 0 <!--- Seconds. --->
7 ) />
8
9<cfset thecount = 0>
10<cfloop from="9:00 AM" to="10:00 AM" step="#dtHour#" index="i">
11<cfset thecount+=1>
12 <cfoutput>#timeformat(i,"short")#<br /></cfoutput>
13</cfloop>
14
15<cfoutput>
16 #thecount#
17</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:
2<cfloop from="10:00 AM" to="11:00 AM" step="#dtHour#" index="i">
3 <cfset thecount2+=1>
4 <cfoutput>#timeformat(i,"short")# #i#<br /></cfoutput>
5</cfloop>
6<cfoutput>
7#thecount2#
8</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:
2 0, <!--- Days. --->
3 0, <!---
4Hours. --->
5 5, <!--- Minutes. --->
6 0 <!--- Seconds. --->
7 ) />
8<cfoutput>#dtHour#<p></cfoutput>
9<cfset thecount = 0>
10<cfloop from="9:00 AM" to="10:00 AM" step="#dtHour#" index="i">
11<cfset thecount+=1>
12 <cfoutput>#timeformat(i,"short")# #i#<br /></cfoutput>
13</cfloop>
14
15<cfoutput>
16 #thecount#
17</cfoutput>
18<br /><br />
19<cfset thecount2 = 0>
20<cfloop from="10:00 AM" to="11:00 AM" step="#dtHour#" index="i">
21 <cfset thecount2+=1>
22 <cfoutput>#timeformat(i,"short")# #i#<br /></cfoutput>
23</cfloop>
24<cfoutput>
25#thecount2#
26</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:
2<cfset step = 5>
3
4<cfset theTime = "9:00 AM">
5<cfset toTime = "10:00 AM">
6
7<cfloop condition="dateCompare(theTime,toTime) lt 1">
8 <cfoutput>thetime=#timeFormat(thetime)#<br></cfoutput>
9 <cfset theTime = dateAdd("n", step, theTime)>
10</cfloop>
11
12<p/>
13
14<cfset theTime = "10:00 AM">
15<cfset toTime = "11:00 AM">
16
17<cfloop condition="dateCompare(theTime,toTime) lt 1">
18 <cfoutput>thetime=#timeFormat(thetime)#<br></cfoutput>
19 <cfset theTime = dateAdd("n", step, theTime)>
20</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.)
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/blog/1615-ColdFusion-Creat...
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.
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.
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.
http://msdn.microsoft.com/en-us/library/ms187819(SQL.90).aspx
The behavior is *almost* float like, but not quite. And it's definitely annoying :)
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."
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!)