In ColdFusion, when you create a true date/time object, it is represented, behind the scenes, as a Java Date object. Now, this is an undocumented feature of ColdFusion, so everything else in this entry should be viewed through this lens. Over the weekend, while working with UTC date/times, I noticed a change in behavior of the date/time objects between ColdFusion 9 and ColdFusion 10.
To demonstrate, take a look at this code which attempts to get the UTC milliseconds since January 1, 1970:
- <!--- Get the local time and the UTC equivalen. --->
- <cfset localDate = now() />
- <cfset utcDate = dateConvert( "local2utc", localDate ) />
- <!--- Output the millseconds since the epoch. --->
- Server: #server.coldfusion.productVersion#<br />
- <br />
- L: #localDate.getTime()#<br />
- U: #utcDate.getTime()#<br />
This takes the local date and converts it to the equivalent UTC date. It then tries to get the milliseconds from 1970 for both dates. When this is run in ColdFusion 10, we get the following output:
As you can see, the getTime() method calls return the same value in both cases.
When we run this in ColdFusion 9, however, we get the following output:
As you can see here, the getTime() method calls return two different values.
I don't have the best grasp on UTC time; nor do I feel terribly comfortable working with timezones. That said, from a philosophical standpoint, I would expect getTime() to return the same value for both local and UTC dates. Since the milliseconds since epoch is the same all over the world (from my understanding), then converting from one timezone to another (or from local to UTC) should have no bearing on the milliseconds since 1970.
That said, it appears like ColdFusion 10 has the correct behavior. But, this post was more to point out the difference, and less so to speculate as to which case is correct. Remember, this is an undocumented feature anyway. Also, to complicate the matter, a UTC bug has already been submitted for ColdFusion 10, which may or may not be related to this behavior.
Looking For A New Job?
- Wanted: Full-Time ColdFusion Developer at Intoria Internet Architects
- Cold Fusion Senior Developer at Edge Information Management
- Back-End Web Developer-Information Technologist at Michigan State University
According to the WIKI the base date for epoch is 1970-01-01 UTC!
"The time kept internally by a computer system is usually expressed as the number of time units that have elapsed since a specified epoch, which is nearly always specified as midnight Universal Time on some particular date."
The difference between epoch base and another date should be the same. If I had to guess Adobe failed to use UTC for it's starting point and instead is using local time in CF10.
Just curoius. Maybe try the same test but use 1970-01-01 instead of now(). That should verify which one is incorrect.
According to the bug at the end of my post, CF10 seems to be using local time when converting date/time objects to strings. So, it looks like something changed somewhere; but, that's the weird thing! In CF10, the getTime() behavior looks more accurate than it does in CF9.
There's definitely a bug somewhere; just not sure which behavior is wrong.
The actual code that I was using when I discovered the differnece was something like:
- createDate( 2012, 10, 1 )
... which also failed. So, it doesn't look like it matters how the date was created :(
I may be way off here but one thing I've run into several times is the auto conversion of long integers into scientific notation. I wonder if it's doing some conversion internally to scientific losing a lot of precision.
i suspect that cf9 sees the converted datetime as if its in the server tz when it does a getTime(). its offset is UTC+7 here (in bangkok).
so your example has broken some of the cardinal rules of tz handing w/cf:
* cf considers all date-times to be in the server's timezone, cf doesn't care about your intentions, just the server's timezone.
* ff cf handles any date-times, these will be unmercifully converted to the server's timezone.
I only have coldfusion 8 and 9 on this computer so cant test at moment but I believe this has more to do with a change on how coldfusion store time then with a change with the gettime() function.
What I mean is in coldfusion 9 and earlier the time zone of the data is not part of the data set.
Therefore the getTime() function has no clue what the timezone of the data and therefore it always assume it in UTC. If you assume it in UTC instead of local time am betting the results will be correct.
Coldfusion 10 I believe does stores the timezone as part of the dataset. Actually I have no way to test this but it would not surprise me if internally it always store the time in UTC plus a offset value base on the time zone you chose. I believe DateTimeFormat is a new function to coldfusion 10 and it takes in timezone as a paremeter.
Therefore in Coldfusion 10 getTime() knows the timezone (or it always getting passed in as the correct UTC time) of the data and therefore can correctly calculate milliseconds since epoch.
Once I get home I can better test this just don't have access to coldfusion 10 where am at now.
Yeah, I'm definitely starting to take it to heart that ALLLLL ColdFusion date manipulations are done in the server's timezone :D Basically, it looks like the only thing I'll be doing in UTC is actually storing the date in the database. Everything else will be in local time. Ideally, I'd love to update the server to run in UTC (if that is a thing). But, there's two reasons I don't see that happening:
1. Tons of data already in the database in EST timezone.
2. Seems like it would make local development harder to test.
Oh sweet, I totally forgot that dateTimeFormat() was *finally* added to ColdFusion! Very cool! A change in internal storage would make this difference seem feasible.
yes you can move the server to the UTC tz but you can also just store the epoch offsets & let the clients sort out their tz. i used to hate when the flash player did that but came to see the wisdom in that approach. i suppose JS has a way to go from epoch offset to local tz too. that's my approach now when we have to deal w/multiple tz.
and no, cf's datetimes have always been & i think still are, decimal days since 31-dec-1899 (like excel & db2--you can test by decimalFormat(now()), you shouldn't see a java epoch sized number). you really don't need any storage changes to produce a formatted date-time string. its *always* been right there in core java's java.text.DateFormat class's getDateTimeInstance method.
There, I am converting the client's local date/time to UTC milliseconds (since 1970) on the client, and then submitting that to the server. Then, I can use ColdFusion to convert the UTC milliseconds to a local time and then to UTC time for database storage.
The awesome thing about ColdFusion storing dates as *days* is that you can do date math pretty easily (ex. "2012/10/02" + 1). It converts it to a numeric date/time value; but, as long as you are aware of that, it does make some things a lot easier to do.
we also discovered the problem and Adobe sent us a patch.
If you do some more investigation you will find that cf_sql_timestamp in cfqueryparam also behaves strange.
Regardless whether you supply an utc converted date or not, the record in the database will always be local time (as far as I remember).
Our typical usecase is that we have a datetime field in an mssql database and use cf_sql_timestamp to insert the value. This worked fine in CF9 and did not work as expected in CF10.
A programmatic solution is to not use dateconvert anymore but to always use datetimeformat and append the utc timezone parameter. This works as expected.
But that way your code base will either be cf9 OR cf10 compatible, not both.
We internally used a UDF that uses either function (dateconvert or datetimeformat) based on the cf server version.
forgot to mention:
that's the cfqueryparam related bug that a coworker mailed to the support team.
and the udf that should work for both product versions is:
- <cffunction name="utc" returntype="date" access="public" output="false">
- <cfargument name="timestamp" type="date" required="false" default="#now()#" />
- <cfif listFirst(server.coldfusion.productversion, ",") gte 10>
- <cfreturn dateTimeFormat(arguments.timestamp, "yyyy-mm-dd HH:nn:ss.lll", "UTC") />
- <cfreturn dateConvert("local2utc", arguments.timestamp) />
Crazy stuff! Thanks for passing this along. I created a follow-up post about inserting into the database (see the comments above); but, I had only tested it in ColdFusion 9, not 10. This stuff is quite irksome :) Thanks so much for the tip re: ColdFusion 10.
@Ben, why not just store the offset & be done with it? really helps to uncomplicate things.
yeah decimal days has its uses, getting day differences is a breeze.
@jan nice solution. that bug report reads like the problem is deep, dark & systemic. i don't let cf touch datetimes if i have to serve to different tz.
Maybe I am not understanding what you mean (I know you've probably explained this to me before on several other timezone-related posts). Are you saying store the user's offset? Or the server's offset?
the epoch offset of that datetime (read that aloud & use a loud booming voice when you say "epoch" & do it in a crowded room).
java epoch offset coming & going. passed & stored on the server, clients will convert to their tz. avoid DST changes, tz hell, etc.
Ok, so you're saying that the client would need report its TZ offset, that would then be stored with the date record?
no, it should return epoch offset (as in the ms since 1-jan-1970) instead of a datetime. store that.
Ah, ok, I see what you're saying. That's more or less the kind of thing I tried in this morning's post. I parse the local date (on the client) into UTC milliseconds; then, I post that value (milliseconds) to the server.
In my example, I am then converting the milliseconds back into a date/time object and storing that. This works fine in CF9; but, it looks like (according to Jan), I'm gonna have issues with that in CF10.
store the epoch offsets. if you need to use them on the server (and you can live w/cf's tz handling) you can re-constitute via datetimeObject.setTime(epoch offset). no fuss, no muss.
When we were first started planning out some database updates, that was our original plan - storing the offsets. As a team, however, we decided that having "human readable" dates in the database was going to be beneficial. I'm not 100% convinced; and, we're still in R&D mode, so nothing is set in stone.
Looks like they made some changes related to date/time conversions.
Looks like Adobe has "pulled back" update 3 because of problems:
[Update: The ColdFusion 10 update 3 will be pulled back shortly because of a few issues in the update. We are working on the fixing these issues. A new update will be released as soon as it is ready. Thank you for your patience and we apologize for the inconvenience caused]
The problems with DateConvert() were driving me crazy until I realized it was a bug:
In the example below the two values are different in CF9 (correct), but the same in CF10 (incorrect). (btw, my TZ is -4:00)
- EpochTimeLocal = DateDiff("s", CreateDate(1970,1,1), now());
- EpochTimeUTC = DateDiff("s", CreateDate(1970,1,1), DateConvert( "Local2UTC", now()));
I was able to work around the bug by using:
- TimeZoneInfo = GetTimeZoneInfo();
- EpochTimeUTC = DateDiff("s", CreateDate(1970,1,1), DateAdd("s", TimeZoneInfo.utcTotalOffset, now()));
Yeah, I think the lesson I've learned is that *ALL* date manipulation has to be done in "local" time. Then, the conversion to UTC has to basically be done only when storing to the database or streaming the data back to the client.