Working With Time Zones

my server's 9PM is probably not your 9PM

ColdFusion And Java

JRE (Java Runtime Environment) that powers ColdFusion has the latest version of the Olson Timezone Database. This database provides encapsulated logic for each timezone such that we don't have to kill ourselves trying to manage all the rules and applications.

 

This timezone database can be accessed using the java.util.TimeZone class. Each timezone is referenced by an ID that is specific to a location, timezone, and daylight savings time (DST) combination.

 

The java.util.TimeZone class encapsulates the logic surrounding the GMT (Greenwich Mean Time) and DST (Daylight Savings Time) rules while the java.util.GregorianCalendar class allows us to navigate through dates and times in the given timezone.

 

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:

• Normalize to a common time - preferably UTC.
• Keep the original time and time zone information.

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.

Time in different time zones

Date	Europe/Brussels	America/New York	London
March 13, 2009	15:46	10:46	14:46

  

 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.

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) 

 

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.

 

 Library Classes

The java.util package has a few classes for working with time.

 

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

 

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");

formatterNY.setTimeZone(newYorkTZ);
formatterBR.setTimeZone(brusselsTZ);

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

 

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.

 

Calendar   

GregorianCalendar.

 

Calendar calendar = new GregorianCalendar();
//calendar.setTime(date);
//calendar.setTimeInMillis(time);

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

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

 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

 

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.

 

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

 

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')