May 28, 2006

there is such a thing as timezone hell

and i know because i've been visiting there for the last 2 weeks or so. and yes, it is a few doors down from classpath hell. if you're outside the US but host in the US or if your ColdFusion application deals w/timezones, and accurate datetime data is important you might want to pay attention this.

let me provide a bit of background first.
ColdFusion datetimes are, i think, coldfusion.runtime.OleDateTime. The actual datetime is stored as decimal day offset from the ColdFusion epoch (31-Dec-1899) which is similar to DB2's and Excel's epochs. you can prove this to yourself by doing something like:


which should return 38864.5102546 for a datetime of {ts '2006-05-27 12:14:46'}. btw this also hints at another way to examine day only differences between dates, ie. you can do int(now()) to ditch the time bits. there's something curious about ColdFusion datetime because you can also treat them as if they were core java Dates. for example

<cfset now=now()>

returns the java epoch offset (milliseconds since 1-Jan-1970), 1148707294281 for a datetime value of {ts '2006-05-27 12:21:34'} in the server's timezone. in fact pretty much all of the core java Date class methods (though many are deprecated) will work on a ColdFusion datetime. you can use toString(), toLocaleString(), toGMTString(), setYear(), etc. though if do use these methods you'll have accomodate core java's Date "quirks" such as years being year-1900, months starting at zero (ie january is month zero in core java), as well as handle tricky data type issues (mainly for setTime() which is looking for a long).

now to the heart of the issue.
it's important to understand that ColdFusion references all datetime objects relative to the ColdFusion server's timezone. if you dump coldfusion.runtime.OleDateTime you'll see a field for TimezoneOffset (similarly core java's Date has a TimezoneOffset getter but no setter). something's gotta go there, might as well be the server's timezone data (i'm sure more thought went into this than that but you get my drift). all datetimes are server datetimes as far as ColdFusion is concerned.

if your application must handle multiple timezones, ideally you'd want to store your datetimes as UTC and convert this to whatever timezone you needed. in fact that's the whole purpose of the timezone CFC. all well and good but all datetimes are server datetimes as far as ColdFusion is concerned. while your intention is that all datetimes are UTC, your ColdFusion server doesn't see that and dependng on your server's timezone, strange stuff can happen. the clearest example is what happens to datetimes on the cusp of Daylight Savings Time (DST) on ColdFusion servers in timezones that use DST (like most of the US-based ColdFusion servers). a datetime of 2006-04-02 02:01:00.0 on those servers would never exist, it would automagically get flopped over to 2006-04-02 03:01:00.0 because ColdFusion doesn't see your UTC timzone only the server's timezone. so for an hour or so twice a year, your datetimes would be wrong by an hour. oops.

lets review some potential workarounds.
  • by far the easiest solution to this problem is to simply set your server's timezone to UTC and bob's your uncle. no fuss, no muss. however i think this won't be a viable solution for many applications hosted on shared servers, so lets look at other workarounds
  • a slicker solution would be for Adobe to provide a setTimeZone() function similar to setLocae() that would force the ColdFusion server to use the selected timezone for all it's datetime operations. if you agree with me on this by all means let the ColdFusion team know.
  • a messier but certainly more portable and near term ready approach would be to use an epoch offset instead of a "real" datetime. two solutions that come to mind are to convert all your datetimes to java epoch offsets as described above or icu4j's universal time scale. right now this is the approach we're going to implement in the new version of the timezone CFC.

i'd like to thank wayne bianca for starting this research in the first place (though given what i've been doing for the last two weeks, i think ignorance is bliss) and spike for helping me stop overthinking the solution.


At 5/28/2006 11:05 AM, Anonymous SiamesePurr771 said...

Hey Paul, I have also put a little thought into this and determined that setting your server to GMT would be cleanest.

However, I also think that dateformat() should take a timezone offset, or at least an "convertToUTC" argument. The reason we don't all just use UTC in everything everywhere is because we want to SEE what time it is where we are, and SEEing time is dateformat() (oh, and timeformat()).

At 5/28/2006 12:31 PM, Blogger PaulH said...

it would be simpler to have a setTimeZone() function.

if your server is UTC tz, then you can use the current version of the timezone CFC to cast your dates to whatever tz you need.

At 8/06/2009 9:03 PM, Anonymous Anonymous said...

I think Railo (the open source CFML engine) put a lot of thought into this, each hosted "site" can have its own timezone settings.


Post a Comment

<< Home