Why is my timestamp shifted in timezone?

Question

I have this date in a PostgreSQL 9.1 database in a timestamp without time zone column:

2012-11-17 13:00:00

It's meant to be in UTC, and it is, which I've verified by selecting it as a UNIX timestamp (EXTRACT epoch).

int epoch = 1353157200; // from database
Date date = new Date((long)epoch * 1000);
System.out.println(date.toGMTString()); // output 17 Nov 2012 13:00:00 GMT

However, when I read this date using JPA/Hibernate, things go wrong. This is my mapping:

@Column(nullable=true,updatable=true,name="startDate")
@Temporal(TemporalType.TIMESTAMP)
private Date start;

The Date I get, however, is:

17 Nov 2012 12:00:00 GMT

Why is this happening, and more importantly, how can I stop it?

Note that I just want to store points in time, universally (as java.util.Date does), and I couldn't care less about timezones, except that I obviously don't want them to corrupt my data.

As you've probably deduced, the client application which connects to the database is in UTC+1 (Netherlands).

Also, the choice for the column type timestamp without time zone was made by Hibernate when it automatically generated the schema. Should that maybe be timestamp with time zone instead?

Answer 1

If a database does not provide timezone information, then the JDBC driver should treat it as if it is in the local timezone of the JVM (see PreparedStatement.setDate(int, Date)):

Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.

The Javadoc and the JDBC specification do not explicitly say anything about ResultSet etc, but to be consistent most drivers will also apply that rule to dates retrieved from the database. If you want explicit control over the timezone used, you will need to use the various set/getDate/Time/Timestamp methods that also accept a Calendar object in the right timezone.

Some drivers also provide a connection property allowing you to specify the timezone to use when converting to/from the database.

Answer 2

I've found that changing the column type to timestamp with time zone fixed the problem. I'll have to convert all my other timestamp columns to that as well then.

From this I conclude that Hibernate does not read timestamp without time zone columns as in UTC, but as in the local timezone. If there is a way to make it interpret them as UTC, please let me know.

Answer 3

There are several solutions to solve the problems:

1) The easiest way is to set the time zone in the JDBC connect string - as long as the database supports this.

For MySQL you can use useGmtMillisForDatetimes=true to force the use of UTC in the database. As far as I know Postgres does not support such an option, but I might be wrong because I don't use Postgres.

2) Set the default time zone in your Java client program with

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

Disadvantage: There you also change the timezone where you don't want, for example if your program has an UI part.

3) Use a mapping with a special getter only for Hibernate: In your Mapping file

<property name="myTimeWithTzConversion" type="timestamp" access="property">
  <column name="..." />
</property>

and in your program you have get/setMyTimeWithTzConversion() for the access only by hibernate to the member variable Timestamp myTime, and in this getter/setter you do the timezone conversion.

We finally decided for 3) (which is a bit more programming work), because there we didn't have to change the existing database, our database was in UTC+1 (which forbids the JDBC connect string solution) and that didn't interfere with the existing UI.