Twitter: raymondcamden


Address: Lafayette, LA, USA

Fascinating issue with createTimeSpan

06-18-2009 5,008 views ColdFusion 13 Comments

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:

view plain print about
1<cfset dtHour = CreateTimeSpan(
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:

view plain print about
1<cfset thecount2 = 0>
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:

view plain print about
1<cfset dtHour = CreateTimeSpan(
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:

view plain print about
1<!--- number of minutes --->
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.)

13 Comments

  • Commented on 06-18-2009 at 1:35 PM
    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/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.
  • Commented on 06-18-2009 at 1:51 PM
    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.
  • Jeff Price #
    Commented on 06-18-2009 at 2:39 PM
    I didn't know you could use times in loops like this either! Very cool. Ben, I like your solution of using CreateTimeSpan.
  • Russ Shakespear #
    Commented on 06-18-2009 at 4:39 PM
    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/blog/684-Looping-Over-Time...
  • Commented on 06-19-2009 at 12:11 AM
    Just remember that no matter what you do you cannot change the future - just ask the people on LOST!
  • Jeff S. #
    Commented on 06-19-2009 at 10:18 AM
    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.
  • Roland Collins #
    Commented on 06-19-2009 at 4:18 PM
    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.
  • Commented on 06-19-2009 at 4:35 PM
    @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.
  • Roland Collins #
    Commented on 06-19-2009 at 4:50 PM
    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/en-us/library/ms187819(SQL.90).aspx

    The behavior is almost float like, but not quite. And it's definitely annoying :)
  • Commented on 06-19-2009 at 4:59 PM
    @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."
  • Roland Collins #
    Commented on 06-19-2009 at 7:10 PM
    You're right - I'm multitasking too much today. I meant to say days, fractional seconds :)
  • Commented on 06-19-2009 at 7:12 PM
    @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!)
  • Commented on 06-19-2009 at 8:21 PM
    @JeffS: Interesting. I didn't know the docs explicitly said not to use anything but integers. That's an important note.

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty