1. Introduction
2. Library Classes

1. Introduction

Often applications are written for deployment on a single server and for users in a single country - e.g. Belgium. As a consequence, localizations of languages and time zones are retrofitted when new requirements or technical realities make us think about these issues.

So far, there is no problem. However, this mindset often leads to a particular kind of problem. The analysts and developers implicitly interpret the value for time as being in the time zone they're in.

Now let's add a requirement to show times localized for some new customers in New York. The fact that we started with our local time in the database intuitively leads to adding a 'flag' to indicate the time in the database column is to be interpreted as in either the Europe/Brussels or the America/New York time zone. Experience learns that this is a bad idea.

Mapping date/time to a database when using different time zones, requires some thought. It all looks very simple to begin with but I've seen a lot of silly mistakes being made...

There are basically two distinct kind of requirements when working with time zones:

The first case can be applied in most situations. As an example consider that we want to create visibility of the whereabouts of a mailed parcel on a global trip. It is sufficient to register each event in UTC and show it to users in their own local time.

The second case applies for example when we want to preserve the time and time zone as communicated by someone. E.g. a customer in New York talking to support in London for a Service hosted in Brussels. The support person should communicate with the customer relative to his time zone: "You reported a service error today at 9:30 AM and yesterday at 9:15 AM". We could deduce the customer's time zone from his location - but perhaps he normally works from Los Angeles instead of New York. A conversation a few weeks later reminding him of the problem he reported as "the 6:15 AM incident" would be utterly confusing.

Look at a few concrete time conversions for different time zones:

Time in different time zones
Date Europe/Brussels America/New York London
March 13, 2009 15:46 10:46 14:46
March 30, 2009 15:46 09:46 14:46
January 15, 2009 15:46 09:46 14:46

As you can see, most of the time there is a difference of 6 hours between Brussels and New York. Except on March 13th. This is because the change to DST is on 1 AM local time on the second Sunday of March (8th) in the US, in the EU this happens on the last Sunday (29th) of March at 2 AM UTC . In the EU therefore, all countries switch at the exact same moment. As illustrated by the fact that London is always exact one hour earlier than Brussels - that was not always the case, however.

A public domain database Zoneinfo exists that is the basis for time zone data in Unix variants like Linux, OS X, Solaris etc. and is used in Java too.

If you do not normalize your data to UTC, you constantly have to keep track of the date, time and the time zone it is recorded in. This is a source of difficult to find bugs, since they occur not very often and they are not always immediately identifiable as problems. When generating BI reports, for instance, a day that has 24 hours instead of 23 (Sunday 29, 2009 in Brussels has only 23 hours) would probably not be noticed, but could have legal or financial consequences.

But sometimes consequences can be graver. Bad assumptions and servers in different time zones can play cruel tricks with your data.

When working with local time and DST , when switching back to winter time - e.g. Sunday October 25th, 2009 in Brussels - we turn the clock back an hour at 3:00 AM, 2:30 for instance happens twice that day and the day is 25 hours long. Be sure to eliminate that ambiguity in your application.

Remark that time zones also contain additional information such as rules and date/time for DST that are subject to arbitrary political decisions. It is not a simple mathematical issue.

1.1. A Practical Solution

What I propose is to keep all the time data in your database in UTC instead. This has a few advantages:

  • The times in the database can be compared, visually and in SQL WHERE clauses.
  • Using Java, you don't have to do any conversions when reading or writing to the database.
  • When changing DST , values don't jump when transitioning to summer or winter time.

There is a small price to pay: manual interventions (mainly INSERT and UDPDATE) should take into account that UTC must be used, not local time.

Let's illustrate that with a concrete example:

Mapping dates normalized to UTC (using java.util.Date)
Mapping dates normalized to UTC (using java.util.Date)

A user John in New York enters a date/time for a planned teleconference with his colleague Mark in Brussels. He types 2009-07-06 09:30 , the application parses that to a Date - containing a long value of 1246887000000. This Date represents 14:30 UTC . It is inserted as such in the database.

On the other side of the Atlantic ocean, Mark checks the date of the planned meeting, the application reads the date from the database - it still represents 14:30 UTC and contains the same long value of 1246887000000. Mark's session contains a formatter that formats this date to 2009-07-06 15:30

The basic point is that each user's session contains a SimpleDateFormat , configured to the time zone of that particular user and parses or formats localized string representations of the actual (UTC) date in the database.

John would have probably preferred to enter the text 07/06/09 09:30 AM instead, and Mark would have preferred 60/07/09 15:30 , but that would complicate matters. So I chose to use an ISO style format.

In a real world example, users probably prefer to use a clickable calendar instead of a plain text entry.

2. Library Classes

The java.util package has a few classes for working with time. Use them. There are also some wrapper or adapter classes in java.sql , if possible, avoid them - there are some gotchas... and you don't need them when using Hibernate.

2.1. Date

java.util.Date is a wrapper for a long value that corresponds with the number of milliseconds since January 1, 1970, 00:00:00 UTC. You can obtain the current time using new Date() or System.out.currentTimeMillis() .

Now let us create a date instance and set it to a time, long, long ago. The 23rd day counting from the beginning of the year 1970. And that is in UTC!

We make one Date, and use SimpleDateFormat to render the date in the context of a particular time zone.

long time = 1000*60*60*24*23;
Date date = new Date(time);

TimeZone brusselsTZ = TimeZone.getTimeZone("Europe/Brussels");
TimeZone newYorkTZ = TimeZone.getTimeZone("America/New_York");

SimpleDateFormat formatterNY = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
SimpleDateFormat formatterBR = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");


System.out.print("New York: ");
//New York: 1970-01-23 19:00:00.000
System.out.print("Brussels: ");
//Brussels: 1970-01-24 01:00:00.000

Want to create a Date from manual input? You can use the formatter to do that:

Date date2 = formatterBR.parse("2009-06-21 22:57:53.952");
//Sun Jun 21 22:57:53 CEST 2009
Date date3 = formatterBR.parse("2009-06-21 22:57");
//throws java.text.ParseException: Unparseable date: "2009-06-21 22:57"

2.2. TimeZone

A standard Java install contains all time zone information, it is maintained for all timezones and gets updates - as long as you apply them.

For server side applications with multiple concurrent users, you can offer the user choices for a preferred timezone. You can obtain the available namesas shown in the code fragment below. Do not set the default timezone in that case, it would affect all concurrent users if you relied upon it.

Be careful, timezones not only differ by the time offset relative to UTC, but also by the DST change rules.

String[] tzNames = TimeZone.getAvailableIDs();
String tzName = tzNames[n];
TimeZone timezone = TimeZone.getTimeZone(tzName);

Whatever you do, NEVER use all the deprecated methods on the Date class. If you need to calculate dates, use the Calendar class. if you need to parse dates, use SimpleDateFormat

These methods return values in the local time zone, i.e. the time zone obtained by TimeZone.getDefault() . They are somewhat awkward to use, e.g. months are zero based where 0 represents January etc.

2.3. Calendar

Here we use a GregorianCalendar. We can set all the values at once with one of the 4 set(...) methods. We can also set the current time for the calendar with a Date or time in 'millis'.

Calendar calendar = new GregorianCalendar();

calendar.set(2009, Calendar.AUGUST, 21);//Gotcha: months are 0-based, January == 0, December == 11
calendar.add(Calendar.DATE, 28);

//The time part was the time when new GregorianCalendar() was executed
//2009-09-18 13:12:53.341

2.4. Database support for timezones

Databases have some support for working with date/time values taking into account the existence of timezones. Most support is focused on interpreting and showing date/time in the timezone of the 'user' or connection. The fact that in a typical architecture, database connections are shared between application users, changing timezones per connection is not a feasible approach

There are no substantial differences. I still recommend to convert everything to UTC before updating or inserting values to the database.

As an illustration of the similarities and differences by vendor, here are some short descriptions of how PostgreSQL, Oracle and MySQL handle timezones:

2.4.1. PostgreSQL 8.1

The manual for PostgreSQL 8.1 touches on some fundamental problems in SQL when handling timezones, quoted from paragraph 8.5.3

PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities.

And then they state

  • "Although the date type does not have an associated time zone, the time type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset may vary through the year with daylight-saving time boundaries."
  • "The default time zone is specified as a constant numeric offset from UTC. It is therefore not possible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries."

These are not flaws of PostgreSQL, they follow from ANSI SQL specifications and apply, for instance to Oracle too.

PostgreSQL has one datatype, TIMESTAMP , that contains date and time data. Two datatypes can be combined with timezone data: TIMESTAMP and TIME, but in accordance with the first remark above, this last one is rather problematic.

2.4.2. Oracle 10g

Oracle supports TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE that enable input and output taking time zones into consideration.

TIMESTAMP WITH TIME ZONE includes a time zone offset in its - literal - value. The data is stored as UTC with an offset. Someone in Brussels or New York will see the same date/time/time zone information.

	'2009-07-15 8:27:00 US/Pacific'
	'2009-12-15 15:33:00 -6:00'

TIMESTAMP WITH LOCAL TIME ZONE includes an implicit time zone offset in its value . The data stored in the database is normalized to the database time zone, the offset is not stored.

The main difference is that the values are interpreted as being in the timezone of the connection (user), and retrieved values are converted to the session's time zone.

In a 3-tiered architecture, this makes little sense since database connections are shared (pooled) between end users.

Oracle has DATE and TIMESTAMP datatypes that contain date and time data

I prefer storing date/time info and time zone info separately. However, with Hibernate mapping Calendar instances instead of Date preserves both in a TIMESTAMP WITH TIME ZONE datatype.

2.4.3. MySQL 6.0

MySQL stores date/time data in UTC and interpretes input depending on the current timezone of the host, server or connection depending on configurations.

MySQL has two built in datatypes that contain date and time data: DATETIME and TIMESTAMP. Neither contains timezone information.

Conversions between timezones can be done with the function CONVERT_TZ

	SELECT CONVERT_TZ('2009-12-01 10:25','+00:00','-6:00')
	SELECT CONVERT_TZ('2009-12-01 10:25','UTC','Europe/Brussels')

When you want to refer to timezones with names such as 'Europe/Brussels' , you might need to load the timezone tables as explained in the manual 4.4.7. mysql_tzinfo_to_sql — Load the Time Zone Tables