how to use utc in postgres timestamp with jdbc PrepareStatement parameter?

Question

I'am using timestamp data type on pg9.4, but there come very strange problem with to_json.

now i am in Shanghai, UTC+08:00 timezone.

see below:

    conn.createStatement().execute("set time zone 'UTC'");

    String sql = "select to_json(?::timestamp) as a, to_json(current_timestamp::timestamp) as b";
    PreparedStatement ps = conn.prepareStatement(sql);
    Timestamp timestamp = new Timestamp(new Date().getTime());      
    ps.setTimestamp(1, timestamp);

    ResultSet rs = ps.executeQuery();
    while(rs.next()){
        System.out.println("a " + rs.getString("a") + ", b " + rs.getString("b"));
    }

output: a "2015-09-24T16:52:42.529", b "2015-09-24T08:53:25.468191"

it's mean when i pass a TIMESTAMP parameter to pg with jdbc, the timezone is still in shanghai, not UTC.

this problem is not due to to_json function, i have make a table with one timestamp column, this problem still exits, the code of above is shortest sample.

how to let's all timestamp work in UTC timezone?

Answer 1

You need to set Calendar tzCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));, Before you create your prepared statement.

UPDATED CODE SNIPPET

    conn.createStatement().execute("set time zone 'UTC'");

    String sql = "select to_json(?::timestamp) as a, to_json(current_timestamp::timestamp) as b";

    Calendar tzCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

    PreparedStatement ps = conn.prepareStatement(sql);
    Timestamp timestamp = new Timestamp(new Date().getTime());      
    ps.setTimestamp(1, timestamp);

    ResultSet rs = ps.executeQuery();
    while(rs.next()){
        System.out.println("a " + rs.getString("a") + ", b " + rs.getString("b"));
    }

This way you will be able to set timezone to UTC in your JDBC call.

If you want to run the whole application/JVM in UTC, set -Duser.timezone=UTC flag while starting JVM.

HTH.