Tuesday, May 28, 2013

How to read dates from Oracle database?

Last week I came across this piece of code, which made the alarm bells ring for me. Can you see the issue with this code?




/**
 * Given a date, strip it of its timezone, and return the date as if it was GMT0
 * @param oracleTimestamp
 * @param localTimeZone
 * @return
 */
private Date toLocalTime(Date oracleTimestamp, TimeZone localTimeZone)
{
    if (oracleTimestamp == null)
        return null;
    Calendar local = localTimeZone == null ? Calendar.getInstance() : Calendar.getInstance(localTimeZone);
    local.clear();
    long localToUtcDelta = local.getTimeZone().getOffset(oracleTimestamp.getTime());
    return new Date(oracleTimestamp.getTime() + localToUtcDelta);
}

The fundamental issue is that there is no concept of locality or time zone in the Date object. This method gets a single and unique moment in time and returns another single and unique moment in time with a possible Gap of a few hours. Since the event has happened in one moment only, therefore one of these values are wrong. It means we start off a wrong value and then try to amend it later. Let us review some background in order to understand the issue a little more.
java.util.Date is time-zone independent and is represented as a long number for the number of milliseconds passed since Epoch, in UTC. As an example, if you put 1,369,656,000,000 in the epoch converter you will get these values back:




So if you had to do magical conversion like this, it is a sign that you (or someone else you have been relying on) have failed to create the right Date object out of this alternative representation. Two common cases are parsing from String and reading from database. When parsing from String values, it is easy to parse using a DateFormat which is set for GMT:

DateFormat gmtDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
DateFormat localDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
gmtDateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
Date date = gmtDateFormat.parse("20130528160000");
System.out.println("GMT: " + gmtDateFormat.format(date) + " Local: " + localDateFormat.format(date));

Let's focus on the more tricky case for the rest of this post, which is reading dates from database (Oracle in this case).

The way DATE and TIMESTAMP Oracle types work is a bit different though. They store year, month, day, ... values separately, as if you had stored the string representation directly. These two fields don't store time zone information. There are other types like "TIMESTAMP WITH TIME ZONE" that do store time zone and are a better fit for times when you care about time zone, but let's assume that we can't use them right now.

JDBC driver converts these two separate formats together for us, but it uses the local time zone of the environment it runs in. If your code runs in GMT+10 time zone, when you store Date(1,369,656,000,000), you end up with "Mon 27 May 2013 10:00:00" in database (with time zone information lost here). Then when you read it back it will be converted back to the right original Date value. This will all break when the time zone in read time is different than that of write time. In order to prevent that, we all agree to store DATE and TIMESTAMP values in GMT, meaning if you look into database you will see "Mon, 27 May 2013 12:00:00".

In plain JDBC it is really easy to read and write correct Date values, without having to jump through hoops, like this:

// WRITING
Timestamp nowTimestamp = new Timestamp(nowDate.getTime());
PreparedStatement insertStmt = conn.prepareStatement(
    "INSERT INTO DATE_TEST_TABLE (ID, DATE_COLUMN, TIMESTAMP_COLUMN) VALUES (?, ?, ?)");
try
{
    insertStmt.setInt(1, getSerial());
    insertStmt.setTimestamp(2, nowTimestamp, cal);
    insertStmt.setTimestamp(3, nowTimestamp, cal);
    insertStmt.executeUpdate();
}
// READING
PreparedStatement selectStmt = conn.prepareStatement(
    "SELECT ID, DATE_COLUMN, TIMESTAMP_COLUMN FROM DATE_TEST_TABLE ORDER BY ID");
ResultSet result = null;
try
{
    result = selectStmt.executeQuery();
    while (result.next())
    {
        System.out.println(
            String.format("%2s, %s, %s",
                result.getInt(1),
                result.getTimestamp(2, cal).toString(),
                result.getTimestamp(3, cal).toString()
            ));
    }
}

In JPA, it is not as straightforward, but not hard though and requires only a row mapper:

class DateTestObjectRowMapper implements RowMapper
{
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException
    {
        Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
        int id = rs.getInt(1);
        Date dateField = rs.getTimestamp(2, calendar);
        Date timestampField = rs.getTimestamp(3, calendar);
        return new DateTestObject(id, dateField, timestampField);
    }
}
// READING
String sql = "SELECT ID, DATE_COLUMN, TIMESTAMP_COLUMN FROM DATE_TEST_TABLE ORDER BY ID";
List<?> objects = getJdbcTemplate().queryForList(sql);
System.out.println("Objects (wrong values): " + objects);
List<?> objectsMappedGMT = getJdbcTemplate().query(sql, new DateTestObjectRowMapper());
System.out.println("Objects (right values, using row mapper and GMT calendar): " + objectsMappedGMT);

If you have used queryForList() method, you are using your local time zone to convert/parse dates which are stored in GMT. You get the WRONG values back, it is not only that they are not local dates, there is no such thing as local date.


Download the complete code to experiment more for yourself. Run OracleDatePlainJdbcTest and OracleDateJpaTest classes to get started.