java - JDBC 将日期转换为 UTC

Question

我有一些代码在 Postgres 表中插入时间戳。下面是插入时间戳的字段的定义:

datelast timestamp without time zone

我使用此 Java 代码更新字段中的数据:

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();

我的问题是它插入了 UTC 时间戳而不是我的本地时间戳(东部时间)。因此，当我检查表中的数据时，我看到“2010-02-08 19:07:21.261”而不是“2010-02-08 14:07:21.261”。

实际上，我希望在旧服务器上运行这段代码，但在迁移代码后，我遇到了那个问题。问题不在于 Postgres，因为我仍然使用相同的数据库。我还检查了操作系统时区，它们是相同的。我还尝试了“System.out.println("TZ = "+ TimeZone.getDefault());"我在两台服务器上获得相同的时区。所以我的结论是，JDBC 驱动程序在将日期插入表之前转换为 UTC 格式的日期。

谁能帮我弄清楚为什么要转换时间戳？

谢谢

Answer 1

正如 Paul Clapham 提到的，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).

手册的这一部分指的是timestamp with timezone，但可以假设相同的内部存储适用于without timezone