JDBC converting dates to UTC

Question

I have some code inserting a timestamp in a Postgres table. Here is the definition of the field where the timestamp is inserted:

datelast timestamp without time zone

I use this Java code to update data in the field:

PreparedStatement sqlStatement = connection.prepareStatement(
        "UPDATE datetest SET datelast = ? WHERE id = ? ");
    sqlStatement.setTimestamp(1, new java.sql.Timestamp((new Date()).getTime()));
    sqlStatement.setInt(2, 1);
    sqlStatement.executeUpdate();

My problem is that it inserts the UTC timestamp instead of my local timestamp (eastern time). So when I check the data in my table, I see "2010-02-08 19:07:21.261" instead of "2010-02-08 14:07:21.261".

Actually, I had this code running has I would like to on an old server, but after migrating the code, I got that problem. The problem is not whit Postgres because I still use the same DB. I also checked the OS timezone and they are the same. I also tried a "System.out.println("TZ = " + TimeZone.getDefault());" and I get the same timezone on both servers. So my conclusion is that the JDBC driver is converting the date in UTC before inserting it in the table.

Can anyone help me to figure out why the timestamp is converted?

Thanks

Answer 1

As Paul Clapham mentions, postgres does indeed always store timestamp values in UTC:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

This section of the manual is referring to timestamp with timezone but one can assume that the same internal storage applies to without timezone