Viewing By Entry / Main
May 27, 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 to 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:

<cfoutput>#javacast("double",now())#</cfoutput>

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()>
<cfoutput>#now.getTime()#</cfoutput>

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 timezone 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 setLocale() 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.

Comments

After reading this article (http://www.javaworld.com/javaworld/jw-10-2003/jw-1003-time_p.html) I thought maybe using CreateObject("java", "java.lang.System").setProperty("user.timezone", "GMT") in CFML would change the default timezone, but seems it doesn't :( Only way to set server time zone to GMT is then to either set it on OS level, or with -Duser.timezone=GMT extra argument in jvm.config.


I casted my vote for a setTimeZone() function. That would be a friendly and elegant solution.

Massimo


erki,

you have to stop/re-start cf for it to pick up any tz changes.

massimo,

thanks.


I have a very similar problem at present.

what i do is store times in the DB in the Server time zone and do conversions back into the current locale when displaying the date.

this makes cfquery's simler because i can do things like

SQL: WHERE publish_date >= #createODBCDateTime(now())#

however when doing the insert/update it forces me to do the conversion from user locale into server time.

at least this way instead of using UTC the conversion is only 1-way rather than 2-way.

However there is an issue with DST crossovers using this method.


pat,

yes we're looking at using server tz for java epoch offsets. right now you really can't use a datetime and still not have DST issues.


Holy crap my head hurts just reading this. Paul - did the recent CF DST announcements affect any of this? I didn't read them but I'm wondering if/how they impacted what you've said here.


todd, no, this issue is kind of fundamental to the way cf handles datetimes.

in any case, update your JDK to handle the new DST changes. (for once) sun's ahead of the game on this ;-)


I had a strange issue with timezone.cfc, on a cfmx7 server, with a jvm version 1.4.2_11 installed.

Yesterday (3/11) at about 6:45PM (CDT) when I ran a castFromServer, the results were an hour earlier for any locale which was under DST.

However, this morning (3/12) castFromServer showed the DST corrected time.

Anyone else see a similar issue?


somebody on cftalk seems to have seen the same thing. i had thought the culprit was dateconvert() but it seems it's working as expected.

kind of confused too.


Hello Paul, Can you provide an example (a piece of CF code) on how to convert a CF datetime to a java epoch offset with icu4j ?

I use your universalTime.cfc with icu4j, but the output is a BigDecimal and I can't cast it with CF and JavaCast function.

Any example or experience will be welcome :-)

FYI we use since three years your timezone.cfc with a global application and several timezones (, all is ok except an hour or twice a year, we have some wrong dates)

Thanks.


Hi Paul,

I'm converting some historical data from US Central Time to UTC, and the castToUTC function wants to create overlapping values starting at midnight on March 12.

Original (Central Time) --> Converted (UTC) 2007-03-11 23:58:00.0 --> 03/12 05:58 AM 2007-03-11 23:59:00.0 --> 03/12 05:59 AM 2007-03-12 00:00:00.0 --> 03/12 05:00 AM 2007-03-12 00:01:00.0 --> 03/12 05:01 AM

Has this error been solved?

Thanks, Matt