如何在带有jdbc PrepareStatement参数的postgres时间戳中使用utc? [英] how to use utc in postgres timestamp with jdbc PrepareStatement parameter?

问题描述

我正在pg9.4上使用 timestamp 数据类型，但是to_json出现了一个非常奇怪的问题.

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

现在我在UTC +08:00时区在上海.

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

请参见下文

    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"));
    }

输出: a"2015-09-24T16:52:42.529"，b"2015-09-24T08:53:25.468191"

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

这是指当我使用jdbc将TIMESTAMP参数传递给pg时，时区仍在上海，而不是UTC.

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

这个问题不是由于to_json函数引起的，我已经用一个timestamp列创建了一个表，这个问题仍然存在，上面的代码是最短的示例.

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.

如何让所有时间戳在UTC时区工作?

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

推荐答案

在创建准备好的语句之前，您需要设置Calendar tzCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));.

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

更新的代码片段

    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"));
    }

这样，您将可以在JDBC调用中将时区设置为UTC.

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

如果要在UTC中运行整个应用程序/JVM，请在启动JVM时设置-Duser.timezone=UTC标志.

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

HTH.

这篇关于如何在带有jdbc PrepareStatement参数的postgres时间戳中使用utc?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！