How to get UTC timestamps from JDBC+postgreSql timestamp?

Question

I created a table like like this in PostgreSQL:

create table myTable (
    dateAdded timestamp(0) without time zone null default (current_timestamp at time zone 'UTC');
)

I choose "without time zone" because I know that all timestamps that my application works with are always UTC. As far as I got the documentation the only difference to "with timestamp" is that I can supply values in other time zones which will then be converted to UTC. However I want to avoid such automatic conversions because they could hardly do any good if I know that my values are UTC.

When I add a new record in my test table and view the table's content with pgAdmin I can see that the insertion date has been correctly saved in UTC format.

However when I try to select values using JDBC the value gets 2 hours subtracted. I am located at UTC+2, so it looks like that JDBC assumes that the date in the table is not a UTC timestamp, but a UTC+2 timestamp instead and tries to convert to UTC.

Some googling revealed that the JDBC standard dictates something about conversion to/from the current time zone, but that this could be prevented by supplying a Calander to getTimestamp/setTimestamp calls. However supplying a calendar did not make any difference at all. Here is my MyBatis/Jodatime converter:

@MappedTypes(DateTime.class)
public class DateTimeTypeHandler extends BaseTypeHandler<DateTime> {
    private static final Calendar UTC_CALENDAR = Calendar.getInstance(DateTimeZone.UTC.toTimeZone());

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
            DateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, new Timestamp(parameter.getMillis()), UTC_CALENDAR);
    }

    @Override
    public DateTime getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        return fromSQLTimestamp(rs.getTimestamp(columnName, UTC_CALENDAR));
    }
    /* further get methods with pretty much same content as above */

    private static DateTime fromSQLTimestamp(final Timestamp ts) {
        if (ts == null) {
            return null;
        }
        return new DateTime(ts.getTime(), DateTimeZone.UTC);
    }
}

What's the correct way to get UTC timestamps from JDBC+PostgreSQL timestamp source?

Answer 1

Solution

Set UTC as default timezone of your JVM -Duser.timezone=UTC or set your whole OS to UTC.

Background

In Postgres both TIMESTAMP and TIMESTAMP WITH TIMEZONE are stored the same way - number of seconds since Postgres epoch (2000-01-01). The main difference is what Postgres do when it saves timestamp value such as 2004-10-19 10:23:54+02:

• without TZ the +02 is just stripped away
• with TZ a -02 correction is performed to make it UTC

Now the interesting thing is when JDBC driver loads the value:

• without TZ the stored value is shifted by the user's (JVM / OS) TZ
• with TZ the value is considered to be UTC

In both cases you will end up with java.sql.Timestamp object with user's default TZ.

Time Zones

Timestamps without TZ are pretty limited. If you have two systems connected to your database, both with different TZ, they will interpret timestamps differently. Therefore, I strongly advice you to use TIMESTAMP WITH TIMEZONE.

---

Update

You can tell JDBC what kind of TZ it should use when reading timestamp via ResultSet#getTimestamp(String, Calendar). Excerpt from JavaDoc:

This method uses the given calendar to construct an appropriate millisecond value for the timestamp if the underlying database does not store timezone information.

Answer 2

There are a few tricks specific for the Postgres JDBC driver

See https://jdbc.postgresql.org/documentation/head/java8-date-time.html

So when reading you can do

    Instant utc =resultSet.getObject("dateAdded",LocalDateTime.class).toInstant(ZoneOffset.UTC);

If you use a connection pool such as Hikari, you can also specify the time time-zone used by each connection by setting connectionInitSql=set time zone 'UTC'