Play! application using UTC - how to do it and how not to do it

[José Santos]

I would like to share my conclusions on how to set up a Play! application using MySQL to use UTC.

The idea is to have all date/time information stored in UTC and to only translate such information to specific timezones when displaying it to users (who have their own timezone settings).

This should be achieved regardless of the underlying operating system timezone, therefore allowing for deployment everywhere.

Date/time information items that should be in UTC include:

- Calendar/Date items such as persistent instance variables in entity classes that are mapped to corresponding MySQL date/time columns
- Time stamps used in CRON expressions within job classes
- Time stamps in log files

1. How to do it

1.1 Set DB timezone to UTC

MySQL may be using the SYSTEM timezone which corresponds to the current operating system one. The timezone may be fetched using:

SELECT @@global.time_zone, @@session.time_zone;

In order to set MySQL's timezone to UTC:

- Edit /etc/mysql/my.cnf
- Below [mysql], add: default-time-zone='+00:00'
- Restart MySQL
- Confirm that the new timezone is set by executing the aforementioned SQL statement

1.2 Set JVM timezone to UTC

JVM's default timezone is set to the operating system one. In order to set JVM's timezone to UTC, Play! should be started using:

play run -Duser.timezone=UTC

2. How not to do it

2.1 Programmatically setting the default timezone to UTC

Setting the default timezone to UTC in a Bootstrap job (using TimeZone.setDefault(TimeZone.getID("UTC"))) works for CRON expressions in Play! jobs (expressions are correctly interpreted as being in UTC).

However, timestamps logged by Log4J show in the operating system timezone. My guess is that Log4J is loaded before the Bootstrap job i.e. it does not take into account the timezone set in the Bootstrap class. A way to achieve this is to specify the timezone in the conversion pattern:

log4j.appender.RollingWarn.layout=org.apache.log4j.EnhancedPatternLayout
log4j.appender.RollingWarn.layout.ConversionPattern=%d{ABSOLUTE}{UTC} %-5p ~ [%X{uid} %X{sid}] %m%n

Note that class EnhancedPatternLayout is required. This class belongs to the Apache Extras Companion for Apache log4j library which is not included with Play! i.e. it needs to be added to the application's lib directory.

Another problem that results from programmatically setting the timezone in a Bootstrap job is the translation between Calendar/Date objects and corresponding MySQL date/time types. Similarly to Log4J, the MySQL driver may be loaded before the Bootstrap job which means it ignores the set timezone. I tried different combinations of JDBC connection string properties for setting the timezone, but none of them worked i.e. Calendar/Date objects got saved/loaded in/from the DB with wrong timezones.

3. Conclusion

In order to set up a Play! application using MySQL to UTC, both JVM and MySQL timezones must be set to UTC.

Setting the default timezone to UTC in a bootstrap method (instead of using JVM option user.timezone) makes job CRON expressions correctly refer to UTC but Log4J lines show in the operating system's timezone and JVM/DB date/time values translation does not work.

The main advantage of setting up UTC in both JVM and MySQL is that there's no need to configure anything else regarding date/time conversion between JVM/DB (e.g. JDBC connection string). Additionally, programming is also simplified, as one can just assume that the timezone is set to UTC everywhere:
- Calendar.getInstance() (and other Java date/time related methods) returns now in UTC (no need to use the factory method that accepts the timezone as parameter)
- select now() (and other MySQL date/time related functions) returns now in UTC.

Therefore, one only needs to worry with timezones when displaying information to users.

[Miel]

Thanks for sharing.  

Note, in my.cnf, i believe the variable should be in the [mysqld] section, not [mysql].

If put in [mysql], mysql command exits with error: mysql: unknown variable 'default-time-zone=+0:00'

(using 5.5.29)

[Marc Siegel]

Hi, I realize this is an old thread, but it may be worth noting: I don't believe you actually need to set the MySQL server's time.

One can just use the JDBC url params shown here:
    http://stackoverflow.com/questions/10488529/gettimestamp-does-timezone-converstion-twice-in-mysql-jdbc-connector

That will ensure that the mysql connections treat times as UTC. As long as JVM time is UTC, I believe that covers it?

-Marc

[Marc Siegel]

For anyone later, I'd like to clarify and confirm.

To ensure UTC through a Play App with MySQL, you must:

 

 1.  Ensure that the JVM is always run in UTC time

      (for example, -Duser.timezone=GMT)

 2.  Use appropriate JDBC url params to MySQL Connector/J:

      (for example, jdbc:mysql://localhost/dbname?useGmtMillisForDatetimes=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&useTimezone=true&serverTimezone=UTC

I can confirm that this will result in UTC being read and written to the DB, regardless of the app server's and db server's timezones, and regardless of the mysql server process timezone.

Thanks,

-Marc