Wednesday, December 23, 2009

Timezones in our CF app - ICU4J

I posted this recently to our Vancouver User Group blog, I thought I'd repost here. I'd love feedback!

In my main gig, we've been working on this particular enterprise application for almost two years.

Like any development team, we took the lessons that we learned from our previous applications. We were quite fortunate that we were able to start pretty much from the ground up, and tried to make wise decisons about things like:

  • Using a code framework
  • Integrating or building CMS functionality
  • Finding a balance between having processing in the database vs CF queries and objects
  • An approach to caching
  • Globalization

I thought I would focus on some challenges we faced with respect to globalization, in particular, timezones.

Our application is a what they call a multi-tenant application, where a single code base and database serves multiple client sites. We knew that globalization was an important aspect of the project.

As part of our efforts towards globalization, we made sure to store dates and times in UTC. This way, dates and times could be independant of the front end usage.

Now we needed to work out how to take those UTC dates and display them in a particular timezone.

We created a front end algorithm to determine a user's timezone. This is encapsulated into a function so that we can expand as required. For example, our application focuses much more on a single timezone for each site (as opposed to user specific settings), so early versions could focus on this simplified approach. We store a user's settings in the session scope.

As an aside, getting a visitor's timezone from their browser isn't as straightforward as one might expect. It is easy to get a number repesenting the offset from UTC, but this doesn't necessarily reveal their actual timezone or daylight savings settings.

Here's a link to a recent blog post about one method of determining a visitor's timezone at

Now that we had what we needed, it was time to present and retrieve data based on the required timezone.

This is where ICU4J came in. ICU4J is a java library for International Components for Unicode, for Java, and can be found at Obviously, since it is in Java the library integrates well with ColdFusion.

So we connected ColdFusion to the jar by instantiating the timezone component in the application scope. We created helper functions to display dates and times in the correct formats and timezones. By sitting atop functions like the ICU4J CastFromUTC, we were now able convert properly.

There is a great blog about globalization in ColdFusion at

So we thought all was well. We were wrong.

In our application we had a particular balance of database stored procedures and CF business logic. Many queries required date comparisons, for example reporting. We began passing converted dates into the queries and stored procedures, converting them to UTC before using them at the database level.

Sometimes it felt like a hack, but it worked.

Or maybe not everytime. In one place we generated a large calendar from the database. This calendar had to look at each day, but each day was defined based on the timezone settings of the user. Passing a timezone in wasn't always enough - consider months where the timezone offset changes due to daylight savings.

The database needed to have some of this timezone information available. We obviously needed to have some sort of timezone intellegence on the database side. However, depending on your database of choice, there might not be much in the way of timezone functionality built in.

So we began to consider ICU4J integration. It turns out that for our database platform, this had already been done.

Article at

So using the ICU4J libraries, we can generate and update tables to store all the information we might need about timezones, offsets and daylight savings boundaries. Powerful stuff.

There are some real benefits to this usage, including:

  • Consistency - we're using the same library on both ends
  • Maintenance - we need to stay on top of updates for one library only (ICU4J)
  • Flexibility - we can play to the strengths of CF vs DB and select the best environment for any particular task.

That's the path we're working towards. So far it was been much more work than we expected but it has also been rewarding. We take it step by step, and keep each piece modular so that we can expand over time. We have a stong feeling that we are headed in the right direction - at very least we are trying to carefully consider our options at each phase.

No comments: