Timezone choice for Postgre and things to do in Java

Always use UTC as the timezone for postgre timestamp/date columns, which is considered to be the best practice and recommended by Postgre, e.g.

utc_created timestamptz not null

And you java application is normally using LocalDateTime/LocalDate, if your system is not a global operation.

So there needs to be a conversion in data access layer of you java application. According to some documents, Timestamp with Timezone in Postgresql should be mapped to OffsetDateTime in Java, and vice versa:

import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;

public class MyTimeZoneHelper {
    private static final ZoneId MY_TIMEZONE_ID = ZoneId.of("Australia/Melbourne");

    public static LocalDateTime toLocalDateTime(OffsetDateTime offsetDateTime) {
        ZonedDateTime zonedDataTime = offsetDateTime.toZonedDateTime();
        return zonedDataTime.withZoneSameInstant(MY_TIMEZONE_ID).toLocalDateTime();
    }


    public static OffsetDateTime toOffsetDateTime(LocalDateTime localDateTime) {
        ZonedDateTime zonedDateTime = localDateTime.atZone(MY_TIMEZONE_ID);
        ZonedDateTime utcZonedDateTime = zonedDateTime.withZoneSameInstant(ZoneId.of("Z"));
        return utcZonedDateTime.toOffsetDateTime();

    }
}

And if you are using mybatis, you can have a type handler based on the helper above:

 
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.OffsetDateTimeTypeHandler;
import org.apache.ibatis.type.TypeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.OffsetDateTime;

/**
 * Convert the UTC timestamps in the new databases (postgres) to LocalDateTime, and vice versa
 */
public class MyTimestampWithTimezoneTypeHandler extends BaseTypeHandler<LocalDateTime> implements TypeHandler<LocalDateTime> {

    private OffsetDateTimeTypeHandler delegate = new OffsetDateTimeTypeHandler();


    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType) throws SQLException {
        delegate.setNonNullParameter(ps, i, MyTimeZoneHelper.toOffsetDateTime(parameter), jdbcType);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        OffsetDateTime offsetDateTime = delegate.getNullableResult(rs, columnName);
        return offsetDateTime == null ? null : MyTimeZoneHelper.toLocalDateTime(offsetDateTime);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        OffsetDateTime offsetDateTime = delegate.getNullableResult(rs, columnIndex);
        return offsetDateTime == null ? null : MyTimeZoneHelper.toLocalDateTime(offsetDateTime);
    }

    @Override
    public LocalDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        OffsetDateTime offsetDateTime = delegate.getNullableResult(cs, columnIndex);
        return offsetDateTime == null ? null : MyTimeZoneHelper.toLocalDateTime(offsetDateTime);
    }
}

<resultMap>
...
 <result column="utc_created" jdbcType="TIMESTAMP_WITH_TIMEZONE" property="createdWhen"  typeHandler="MyTimestampWithTimezoneTypeHandler"/>
...

</resultMap>