postgresql JDBC 驱动程序使用客户端时区

Question

我正在使用postgresql-42.2.2.jar jdbc driver和postgresql 9.6服务器。

如何将连接配置为使用服务器时区而不是客户端时区？

查询：

select id, TO_TIMESTAMP(tbl.ended_time / 1000)
FROM tbl
where TO_TIMESTAMP(tbl.ended_time / 1000) >= now()::date + interval '1m'

使用 pgadmin 运行查询我得到以下结果(这是正确的(

"2185"  "2018-06-22 00:02:02-05"
"2186"  "2018-06-22 00:03:01-05"
"2187"  "2018-06-22 00:13:02-05"

但是使用 JDBC 驱动程序运行相同的查询，结果是：

2169 2018-06-22 07:13:01.0
2181 2018-06-22 08:33:02.0
2180 2018-06-22 08:23:01.0
2185 2018-06-22 09:02:02.0
2186 2018-06-22 09:03:01.0
2187 2018-06-22 09:13:02.0

如果没有使用 PGADMIN 运行查询，结果是：

"2169"  "2018-06-21 22:13:01-05"
"2181"  "2018-06-21 23:33:02-05"
"2180"  "2018-06-21 23:23:01-05"
"2185"  "2018-06-22 00:02:02-05"
"2186"  "2018-06-22 00:03:01-05"
"2187"  "2018-06-22 00:13:02-05"

这是代码：

static String URL = "jdbc:postgresql://some_ip:7000/db";
static String USERNAME = "test";
static String PASSWORD = "test";
static String JDBC_DRIVER = "org.postgresql.Driver";
public static void main(String[] args) {
String query = "select id, TO_TIMESTAMP(tbl.ended_time / 1000) FROM tbl where TO_TIMESTAMP(tbl.ended_time / 1000) >= now()::date + interval '1m'";
Connection conn = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement st = conn.createStatement();          
ResultSet rs = st.executeQuery(query);
while (rs.next()) {             
System.out.println(rs.getObject(1) + "--" + rs.getObject(2));
}
rs.close();
st.close();
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} 
}

Answer 1

如此处所述，JDBC 驱动程序使用您的本地时区，而不是服务器时区。

不幸的是，没有简单的方法来更改该行为或查询服务器的时区。

另请注意，使用OffsetDateTime检索数据没有多大帮助：

rs.getObject(2, OffsetDateTime.class)

因为不幸的是，驱动程序返回偏移量为 0 的所有OffsetDateTime，因此时区信息将丢失。

因此，您最好的选择可能是对数据库的时区进行硬编码并在 Java 中计算您的时间限制，或者将时区单独存储在数据库中并查询它。

Answer 2

TL;DR：它与JDBC驱动程序无关。SQL是有缺陷的。

在比较timestamp WITH time zone和timestamp withOUT time zone时，您有什么期望？你的SQL是完全错误的，因此它会产生奇怪的结果。

你对now()::date有什么期望？注意：PostgreSQL 文档没有说明now()会产生哪个时区。它只指定"时区将在那里"，但它甚至可以从一个PG版本更改为另一个PG版本。所以你基本上是在请求"随机值"。

请澄清你想要实现什么，然后编写一个适当的SQL。 例如，如果您想获取时间戳为"在欧洲/柏林时区当天 00：04：00 之后"的记录，那么您可以使用类似

where TO_TIMESTAMP(tbl.ended_time / 1000) >= ((now() at time zone 'Europe/Berlin')::date + interval '4m')::timestamp at time zone 'Europe/Berlin'

问题是没有"一天的开始"，除非你指定你的意思是哪个时区。pgjdbc用默认时区(TimeZone.getDefault()(填充会话，因此它用于隐式转换，但是我强烈建议您不要比较SQL中的with和without时区类型的数据类型，无论您使用什么来执行。