如何从JDBC+postgreSql时间戳获取UTC时间戳？

Question

如何从JDBC+postgreSql时间戳获取UTC时间戳？

21

我在PostgreSQL中创建了一个类似于这样的表：

create table myTable (
    dateAdded timestamp(0) without time zone null default (current_timestamp at time zone 'UTC');
)

我选择“无时区”，因为我知道我的应用程序使用的所有时间戳都是始终使用 UTC。根据文档，与“带时间戳”唯一的区别在于，我可以提供其他时区的值，这些值将转换为 UTC。但是，我想避免这种自动转换，因为如果我知道我的值是 UTC，那么它们几乎不会有任何好处。

当我在测试表中添加新记录并使用 pgAdmin 查看表的内容时，我可以看到插入日期已以 UTC 格式正确保存。

然而，当我尝试使用 JDBC 选择值时，该值会减少 2 小时。我位于 UTC+2，因此看起来 JDBC 假定表中的日期不是 UTC 时间戳，而是 UTC+2 时间戳，并尝试转换为 UTC。

一些谷歌搜索揭示了 JDBC 标准规定了一些关于转换为/从当前时区的内容，但这可以通过向 getTimestamp/setTimestamp 调用提供一个日历来防止。然而，提供日历并没有什么区别。下面是我的 MyBatis/Jodatime 转换器：

@MappedTypes(DateTime.class)
public class DateTimeTypeHandler extends BaseTypeHandler<DateTime> {
    private static final Calendar UTC_CALENDAR = Calendar.getInstance(DateTimeZone.UTC.toTimeZone());

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
            DateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, new Timestamp(parameter.getMillis()), UTC_CALENDAR);
    }

    @Override
    public DateTime getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        return fromSQLTimestamp(rs.getTimestamp(columnName, UTC_CALENDAR));
    }
    /* further get methods with pretty much same content as above */

    private static DateTime fromSQLTimestamp(final Timestamp ts) {
        if (ts == null) {
            return null;
        }
        return new DateTime(ts.getTime(), DateTimeZone.UTC);
    }
}

如何从JDBC+PostgreSQL的时间戳源获取UTC时间戳？

- yankee

你考虑过使用新的Java 8时间API吗？DateTime类以前有这种问题。也许可以使用Java 8中的Instant类代替DateTime。 - Ismael_Diaz

3个回答

1

有一些针对Postgres JDBC驱动程序的特殊技巧。

请参见https://jdbc.postgresql.org/documentation/head/java8-date-time.html。

因此，在读取时，您可以执行以下操作

    Instant utc =resultSet.getObject("dateAdded",LocalDateTime.class).toInstant(ZoneOffset.UTC);

如果您使用像Hikari这样的连接池，您还可以通过设置connectionInitSql=set time zone 'UTC'来指定每个连接使用的时区。

- David Lilljegren

0

Pavel提出的解决方案（添加jvm参数'-Duser.timezone=UTC'）无疑是最好的选择，但如果您没有系统访问权限，则不一定总是可行。

我找到的唯一方法是在查询中将时间戳转换为epoch并将其读取为long。

SELECT  extract(epoch from my_timestamp_colum at time zone 'utc')::bigint * 1000 
            AS my_timestamp_as_epoc
FROM    my_table

然后使用纯JDBC读取它

ResultSet rs = ...
long myTimestampAsEpoc = rs.getLong("my_timestamp_as_epoc");
Date myTimestamp = new Date(myTimestampAsEpoc);

使用iBatis/MyBatis时，您需要将列处理为Long，然后手动转换为Date/Timestamp。不方便且难看。

在PostgreSQL中进行反向操作可以使用以下方法：

SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 
       1421855729000 * INTERVAL '1 millisecond'

话虽如此，JDBC规范并不指定返回的时间戳是否应移动到用户时区；但是，由于您将表列定义为“没有时区的时间戳”，我期望没有时间偏移，而仅记录了纪元被包装在java.sql.Timestamp中。我认为，这是PostgreSQL JDBC驱动程序中的一个错误。由于问题在这个层面上，因此可能没有太多可以做的事情，除非有系统访问权限。

- Luigi R. Viggiano

JDBC规范（或API文档）要求默认情况下将时间视为JVM的当前时区。如果您想使用另一个时区，则可以提供一个处于正确时区的“日历”。由于问题中所做的就是这样，看起来这就是实际的错误。 - Mark Rotteveel

你能指出 JDBC 规范在哪里说明了 JVM 时区是默认的吗？我昨天进行了搜索，但没有找到。 - Luigi R. Viggiano

请查看 PreparedStatement.setTimestamp 和 ResultSet.getTimestamp 的文档。 - Mark Rotteveel

参见setTimestamp(int parameterIndex, Timestamp x)：它根本没有提到任何日历或时区。这个方法委托给setTimestamp(int parameterIndex, Timestamp x, Calendar cal)，并使用null日历是一个实现细节，不是规范要求的。 - Luigi R. Viggiano

1

强调我的部分：“如果没有指定日历对象，则驱动程序使用默认时区，即运行应用程序的虚拟机的时区。” JDBC 的问题在于你需要同时处理规范、API 文档，并且要记住信息并不总是重复的。在没有日历的情况下设置/获取时间戳就是这里提到的情况，即_“如果没有指定日历对象”_。 - Mark Rotteveel

显示剩余2条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Pavel Horal · Accepted Answer

解决方案

将UTC设置为JVM的默认时区-Duser.timezone=UTC，或将整个操作系统设置为UTC。

背景

在Postgres中，TIMESTAMP和TIMESTAMP WITH TIMEZONE以相同方式存储-自Postgres纪元（2000年1月1日）以来的秒数。主要区别在于当Postgres保存时间戳值（例如2004-10-19 10:23:54+02）时，它会执行以下操作：

没有时区时，只需去掉+02
有时区时，执行-02校正使其为UTC

现在有趣的事情是JDBC驱动程序加载值时：

没有时区时，存储的值会被用户的（JVM / OS）时区偏移
有时区时，该值被认为是UTC

在两种情况下，您都将获得具有用户默认TZ的java.sql.Timestamp对象。

时区

没有时区的时间戳相当受限制。如果您有两个连接到数据库的系统，两者的时区不同，则它们将以不同的方式解释时间戳。因此，我强烈建议您使用TIMESTAMP WITH TIMEZONE。

更新

您可以通过告诉JDBC何时读取时间戳来指定它应该使用哪种TZ ResultSet#getTimestamp(String, Calendar)。JavaDoc的摘录：

如果底层数据库不存储时区信息，则此方法使用给定的日历构造适当的毫秒值以生成时间戳。