CFMX applications on servers with different time zones

Initially I found the excellent timezone.cfc from Paul Hastings which did a sterling job.

I created a wrapper cfc for it and with that could simple convert entire queries into the correct time zones, as well as single dates. This was fine for display (if a bit slow) but made some database functionality not work. For example the DB was still operating in Australia/Sydney days and so when doing a group by day report it would by Sydney days not Dublin days as was needed.

After a bit more hunting I discovered that MYSQL can handle time zone conversion very efficiently. The CONVERT_TZ method will do conversions on the queries coming out and values going into the DB.

To make my life a bit easier I've wrapped the functionality up into a little CFC available here.

Usage of the CFC is as follows:

• Init the CFC with a DSN, ServerTimeZone and UserTimeZone (what will be displayed)

Then for columns and values you want changed wrap these functions around the columns name.

• serverToUserTZ will convert values to user local timezone
• userToServerTZ will convert values to server local timezone

It runs with almost no difference in query times (but I haven't benchmarked it).

I still need to verify if the grouping by days in a different time zone works - more to follow.