Posts tagged: time zone

getSynergy(Oracle,Java) = ‘timestamps do not work’

By , May 14, 2009

The excitement about the news of Oracle acquiring Sun is prompting me to write an entry about their synergy.

Let’s take one of the developers’ favorite topics – working with dates in different time zones. Why would anyone ever do that to themselves?!?

Understandably, Oracle decided to not worry about making timezone calculations work properly in their .NET and Java drivers. Calls to function getTimestamp() backing TIMESTAMP WITH TIME ZONE columns return stored time with timezone thrown away for any timezone. For example, if one stored ‘May 13, 2009 15:05:25 -0700’ then resultSet.getTimestamp(colNo) returns ‘May 13, 2009 15:05:25’ ignoring the client timezone. I.e. it is right only in Pacific timezone. This is true for both Java and .NET functions – remarkable bug consistency!

The only seemingly sane way to read timezone info into java.sql.Timestamp is via retrieval of date column as a string that then has to be parsed. Of course, to make matters worse Oracle returns timestamps in the format that neither Java nor .NET can parse using any possible date parsing configuration (2009-5-13 -5:00″)

Below is a snippet of custom code for Java that performs this conversion.

I am not completely sure why Oracle cannot fix their driver. I suspect that they know that people have already built all sorts of hacks that will only work based on their original buggy behavior. I contacted Oracle about similar behavior for .NET – they call this a feature…

However, if it all possible – think through your time zone design first. The old and proven UTC times-only everywhere approach works…

 * Oracle TIMESTAMPTZ, when converted to string, contains six extra digits, all zeroes,
 * tacked on the seconds field.  This is unparseable by the formats used by the DateFormat
 * class.
 * The timezone offset consists of an optional + or - sign, the hours offset, a colon, and the
 * minutes offset, e.g., -5:00.  This is similarly unparseable (we need it to be of
 * the form "-0500").
 * This method massages the given timestamp string, removes and converts the problem data
 * described above, then parses it into a true Timestamp, taking the timezone into account.
public static java.sql.Timestamp convertOracleTimestampTzStrToTimestamp(String dateStr) {
// six trailing zeroes (seconds) followed by the timezone offset: an optional +/-
// sign, one or more digits (the hours), a colon, and one or more digits (the minutes).
  Pattern oracleDateStrPattern = Pattern.compile("(.[0-9]*) ([-\\+]?)(\\d+):(\\d+)$");
  Matcher m = oracleDateStrPattern.matcher(dateStr);
  if (!m.find()) {
    throw new RuntimeException("Invalid date format received: " + dateStr);
  String datePart = dateStr.substring(0, m.start(1));
  String msPart = dateStr.substring(m.start(1), m.start(2)-1);
  int ms = (int) (1000*Float.parseFloat("0" + msPart));
  String signStr = dateStr.substring(m.start(2), m.end(2));
  if ("".equals(signStr)) signStr = "+";
  String hoursStr = dateStr.substring(m.start(3), m.end(3));
  String minsStr = dateStr.substring(m.start(4), m.end(4));
  StringBuffer sb = new StringBuffer(50);
  String msStr = Integer.toString(ms);
  appendZeroPaddedString(sb, msStr, 3);
  sb.append(" ");
  appendZeroPaddedString(sb, hoursStr, 2);
  appendZeroPaddedString(sb, minsStr, 2);
  String javifiedTzStr = sb.toString();

  DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd Z");
  java.util.Date d;
  try {
    d = dateFormatter.parse(javifiedTzStr);
  } catch (ParseException e) {
    throw new RuntimeException("Invalid date format received:" + dateStr);
  java.sql.Timestamp retval = new java.sql.Timestamp(d.getTime());
  return retval;

private static void appendZeroPaddedString(StringBuffer sb, String str, int desiredLength)
  int len = str.length();
  for (int padz = 0; padz < desiredLength - len; padz++ ) {

Panorama Theme by Themocracy