如何从 JDBC+postgreSql 时间戳获取 UTC 时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26064489/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get UTC timestamps from JDBC+postgreSql timestamp?
提问by yankee
I created a table like like this in PostgreSQL:
我在 PostgreSQL 中创建了一个这样的表：
create table myTable (
dateAdded timestamp(0) without time zone null default (current_timestamp at time zone 'UTC');
)
I choose "without time zone" because I know that all timestamps that my application works with are alwaysUTC. As far as I got the documentation the only difference to "with timestamp" is that I can supply values in other time zones which will then be converted to UTC. However I want to avoid such automatic conversions because they could hardly do any good if I know that my values are UTC.
我选择“无时区”是因为我知道我的应用程序使用的所有时间戳都是UTC。就我获得的文档而言，与“带时间戳”的唯一区别是我可以提供其他时区的值，然后将其转换为 UTC。但是我想避免这种自动转换，因为如果我知道我的值是 UTC，它们几乎没有任何好处。
When I add a new record in my test table and view the table's content with pgAdmin I can see that the insertion date has been correctly saved in UTC format.
当我在测试表中添加新记录并使用 pgAdmin 查看表的内容时，我可以看到插入日期已正确保存为 UTC 格式。
However when I try to select values using JDBC the value gets 2 hours subtracted. I am located at UTC+2, so it looks like that JDBC assumes that the date in the table is not a UTC timestamp, but a UTC+2 timestamp instead and tries to convert to UTC.
但是，当我尝试使用 JDBC 选择值时，该值会减去 2 小时。我位于 UTC+2，因此看起来 JDBC 假定表中的日期不是 UTC 时间戳，而是 UTC+2 时间戳，并尝试转换为 UTC。
Some googling revealed that the JDBC standard dictates something about conversion to/from the current time zone, but that this could be prevented by supplying a Calander to getTimestamp/setTimestamp calls. However supplying a calendar did not make any difference at all. Here is my MyBatis/Jodatime converter:
一些谷歌搜索显示，JDBC 标准规定了与当前时区之间的转换，但可以通过向 getTimestamp/setTimestamp 调用提供 Calander 来防止这种情况。然而，提供日历根本没有任何区别。这是我的 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);
}
}
What's the correct way to get UTC timestamps from JDBC+PostgreSQL timestamp source?
从 JDBC+PostgreSQL 时间戳源获取 UTC 时间戳的正确方法是什么？
回答by Pavel Horal
Solution
解决方案
Set UTC as default timezone of your JVM -Duser.timezone=UTCor set your whole OS to UTC.
将 UTC 设置为 JVM 的默认时区-Duser.timezone=UTC或将整个操作系统设置为 UTC。
Background
背景
In Postgres both TIMESTAMPand TIMESTAMP WITH TIMEZONEare stored the same way - number of seconds since Postgres epoch (2000-01-01). The main difference is what Postgres do when it saves timestamp value such as 2004-10-19 10:23:54+02:
在 Postgres 中，TIMESTAMP和TIMESTAMP WITH TIMEZONE都以相同的方式存储 - 自 Postgres 纪元（2000-01-01）以来的秒数。主要区别在于 Postgres 在保存时间戳值时会做什么，例如2004-10-19 10:23:54+02：
- without TZ the
+02is just stripped away - with TZ a
-02correction is performed to make it UTC
- 没有 TZ
+02只是被剥夺了 - 使用 TZ 进行
-02更正以使其成为 UTC
Now the interesting thing is when JDBC driver loads the value:
现在有趣的是 JDBC 驱动程序加载值时：
- without TZ the stored value is shifted by the user's (JVM / OS) TZ
- with TZ the value is considered to be UTC
- 没有 TZ，存储的值由用户的（JVM/OS）TZ 移动
- 使用 TZ，该值被认为是 UTC
In both cases you will end up with java.sql.Timestampobject with user's default TZ.
在这两种情况下，您最终都会得到java.sql.Timestamp具有用户默认 TZ 的对象。
Time Zones
时区
Timestamps without TZ are pretty limited. If you have two systems connected to your database, both with different TZ, they will interpret timestamps differently. Therefore, I strongly advice you to use TIMESTAMP WITH TIMEZONE.
没有 TZ 的时间戳非常有限。如果您有两个系统连接到您的数据库，都具有不同的 TZ，它们将不同地解释时间戳。因此，我强烈建议您使用TIMESTAMP WITH TIMEZONE.
Update
更新
You can tell JDBCwhat kind of TZ it should use when reading timestamp via ResultSet#getTimestamp(String, Calendar). Excerpt from JavaDoc:
您可以告诉 JDBC在通过ResultSet#getTimestamp(String, Calendar). 摘自 JavaDoc：
This method uses the given calendar to construct an appropriate millisecond value for the timestamp if the underlying database does not store timezone information.
如果基础数据库不存储时区信息，则此方法使用给定的日历为时间戳构造适当的毫秒值。
回答by Luigi R. Viggiano
The solution proposed by Pavel (adding the jvm param '-Duser.timezone=UTC') is for sure the best option, if you don't have system access, this isn't always possible to do.
Pavel 提出的解决方案（添加 jvm 参数 '-Duser.timezone=UTC'）肯定是最好的选择，如果您没有系统访问权限，这并不总是可行的。
The only way I found is to convert the timestamp to epochin the query and read it as a long.
我发现的唯一方法是在查询中将时间戳转换为纪元并将其作为long.
SELECT extract(epoch from my_timestamp_colum at time zone 'utc')::bigint * 1000
AS my_timestamp_as_epoc
FROM my_table
Then read it in plain JDBC with
然后用普通的 JDBC 读取它
ResultSet rs = ...
long myTimestampAsEpoc = rs.getLong("my_timestamp_as_epoc");
Date myTimestamp = new Date(myTimestampAsEpoc);
With iBatis/MyBatis, you need to handle the column as a Long, then convert it manually to Date/Timestamp. Inconvenient and ugly.
使用 iBatis/MyBatis，您需要将列作为 Long 处理，然后手动将其转换为 Date/Timestamp。不方便和丑陋。
The reverse operation in PostgreSQL can be done with:
PostgreSQL 中的反向操作可以通过以下方式完成：
SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' +
1421855729000 * INTERVAL '1 millisecond'
That said, the JDBC specification doesn't say that the returned timestamp shall or shall not shift the timestamp to the user time zone; BUT since you defined the table column as 'timestamp without time zone' I would expect no time shifts, but the recorded epoch being just wrapped in a java.sql.Timestamp. For my opinion, this is a bug in the PostgreSQL JDBC driver. Being the problem at this level, then, probably there is not much more that can be done, without system access.
也就是说，JDBC 规范并没有说返回的时间戳应该或不应该将时间戳移动到用户时区；但是，由于您将表列定义为“没有时区的时间戳”，我希望没有时间偏移，但记录的纪元只是包含在java.sql.Timestamp. 在我看来，这是 PostgreSQL JDBC 驱动程序中的一个错误。那么，作为这个级别的问题，如果没有系统访问权限，可能无法完成更多工作。
回答by David Lilljegren
There are a few tricks specific for the Postgres JDBC driver
有一些特定于 Postgres JDBC 驱动程序的技巧
See https://jdbc.postgresql.org/documentation/head/java8-date-time.html
见https://jdbc.postgresql.org/documentation/head/java8-date-time.html
So when reading you can do
所以在阅读时你可以做
Instant utc =resultSet.getObject("dateAdded",LocalDateTime.class).toInstant(ZoneOffset.UTC);
If you use a connection pool such as Hikari, you can also specify the time time-zone used by each connection by setting connectionInitSql=set time zone 'UTC'
如果使用Hikari等连接池，还可以通过设置connectionInitSql= set time zone 'UTC'来指定每个连接使用的时区
