JDBC converting dates to UTC

Question

Hi all,

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

When you say "check the data in my table" I suppose you are using some database-viewing tool? It would help to know how you are getting those values.

It wouldn't surprise me to find that your database stores the values internally as UTC. It would then be up to the software that extracts the data to display it in a chosen time-zone. In fact I wouldn't expect to find that it stores the data internally as something dependent on the default time-zone on the database server, because then changing that default time-zone (or moving the database to a different server with a different default) would change all the data. That would be a bad thing.

Answer 2

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

Answer 3

Hi,

my problem was related to the JAR file for Postgres.

On my old server, I was using a Postgres 7 JAR file and everything was working well. For some reason, it wasn't working anymore on my new server. I replaced the JAR file by the Postgres 8 one and now everything is working well. When I insert a timestamp in the DB, it now inserts my local time, and not GMT time.

Thanks everyone for your help.