Fix timestamp timezone issues by forcing UTC and storing with the timezone
PostgreSQL is pretty insistent on returning results in the timezone
of the client, in this case the JVM. But we want everything in the
JVM to be in UTC, because that's what the native Java date types work
on and operate with.
By forcing values to be set in UTC, and normalized back to UTC by
applying any embedded timezone offset before making the Timestamp
available to the application we can hide this behavior.
Columns in PostgreSQL are marked "WITH TIME ZONE" so Postgres is
able to store everything in UTC, and know for a fact that they
are all UTC values.
Signed-off-by: Shawn O. Pearce <sop@google.com>
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java b/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java
index 27f8a2c..5d2391c 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java
@@ -6,6 +6,7 @@
public class DialectPostgreSQL extends SqlDialect {
public DialectPostgreSQL() {
typeNames.put(Types.VARBINARY, "BYTEA");
+ typeNames.put(Types.TIMESTAMP, "TIMESTAMP WITH TIME ZONE");
}
@Override
diff --git a/src/main/java/com/google/gwtorm/schema/sql/SqlTimestampTypeInfo.java b/src/main/java/com/google/gwtorm/schema/sql/SqlTimestampTypeInfo.java
index 9a71ad3..d97b227 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlTimestampTypeInfo.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlTimestampTypeInfo.java
@@ -14,11 +14,40 @@
package com.google.gwtorm.schema.sql;
+import com.google.gwtorm.jdbc.gen.CodeGenSupport;
import com.google.gwtorm.schema.ColumnModel;
+import org.objectweb.asm.Opcodes;
+import org.objectweb.asm.Type;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Timestamp;
import java.sql.Types;
+import java.util.Calendar;
+import java.util.TimeZone;
public class SqlTimestampTypeInfo extends SqlTypeInfo {
+ private static final TimeZone UTC = TimeZone.getTimeZone("UTC");
+
+ public static void setAsUTC(final PreparedStatement ps, final int col,
+ final Timestamp val) throws SQLException {
+ ps.setTimestamp(col, val, Calendar.getInstance(UTC));
+ }
+
+ public static Timestamp getAsUTC(final ResultSet rs, final int col)
+ throws SQLException {
+ Timestamp s = rs.getTimestamp(col);
+ if (s != null) {
+ final int o = s.getTimezoneOffset();
+ if (o != 0) {
+ s = new Timestamp(s.getTime() + (o * 60 * 1000L));
+ }
+ }
+ return s;
+ }
+
@Override
protected String getJavaSqlTypeAlias() {
return "Timestamp";
@@ -30,6 +59,31 @@
}
@Override
+ public void generateResultSetGet(CodeGenSupport cgs) {
+ final Type typeCalendar = Type.getType(java.util.Calendar.class);
+ cgs.fieldSetBegin();
+ cgs.pushSqlHandle();
+ cgs.pushColumnIndex();
+ cgs.mv.visitMethodInsn(Opcodes.INVOKESTATIC, Type.getType(
+ SqlTimestampTypeInfo.class).getInternalName(), "getAsUTC", Type
+ .getMethodDescriptor(Type.getType(Timestamp.class), new Type[] {
+ Type.getType(ResultSet.class), Type.INT_TYPE}));
+ cgs.fieldSetEnd();
+ }
+
+ @Override
+ public void generatePreparedStatementSet(final CodeGenSupport cgs) {
+ cgs.pushSqlHandle();
+ cgs.pushColumnIndex();
+ cgs.pushFieldValue();
+ cgs.mv.visitMethodInsn(Opcodes.INVOKESTATIC, Type.getType(
+ SqlTimestampTypeInfo.class).getInternalName(), "setAsUTC", Type
+ .getMethodDescriptor(Type.VOID_TYPE, new Type[] {
+ Type.getType(PreparedStatement.class), Type.INT_TYPE,
+ Type.getType(Timestamp.class)}));
+ }
+
+ @Override
public String getSqlType(final ColumnModel col, final SqlDialect dialect) {
final StringBuilder r = new StringBuilder();
r.append(dialect.getSqlTypeName(getSqlTypeConstant()));
