PostgreSQL 9.2 JDBC 驱动程序使用客户端时区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18447995/
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
PostgreSQL 9.2 JDBC driver uses client time zone?
提问by JTShyman
I've run into an interesting challenge using a PostgreSQL database with the PostgreSQL JDBC driver. It seems that the latest version of the driver, 9.2, uses the clienttime zone when performing date/time matches.
我在使用带有 PostgreSQL JDBC 驱动程序的 PostgreSQL 数据库时遇到了一个有趣的挑战。似乎最新版本的驱动程序 9.2在执行日期/时间匹配时使用客户端时区。
This becomes a problem when the server (JasperReports Server) is set to UTC and the database server is set to US/Eastern.
当服务器 (JasperReports Server) 设置为 UTC 并且数据库服务器设置为 US/Eastern 时，这就会成为一个问题。
If I run the following query from a client set to the UTC time zone I get different results using the 9.0 JDBC driver and the 9.2 JDBC driver.
如果我从设置为 UTC 时区的客户端运行以下查询，我会使用 9.0 JDBC 驱动程序和 9.2 JDBC 驱动程序得到不同的结果。
select now(), extract(timezone FROM now()), current_setting('TIMEZONE'), now()-interval '1 hour' as "1HourAgo"
Results using 9.0 JDBC driver:
使用 9.0 JDBC 驱动程序的结果：
now date_part current_setting 1HourAgo
2013-08-26 15:33:57.590089 -14,400 US/Eastern 2013-08-26 14:33:57.590089
Results using 9.2 JDBC driver:
使用 9.2 JDBC 驱动程序的结果：
now date_part current_setting 1HourAgo
2013-08-26 15:41:49.067903 0 UTC 2013-08-26 14:41:49.067903
This is causing a WHERE statement in a query to return incorrect results. For example,
这会导致查询中的 WHERE 语句返回不正确的结果。例如，
WHERE end_time between now() - interval '1 hour' and now()
works as expected using the 9,0 driver but returns no results using the 9,2 driver as the driver appears to be offsetting the value of end_time to match UTC (the client's time zone). The following is a workaround, but an ugly one:
使用 9,0 驱动程序按预期工作，但使用 9,2 驱动程序不返回任何结果，因为驱动程序似乎正在偏移 end_time 的值以匹配 UTC（客户端的时区）。以下是一种解决方法，但很丑陋：
WHERE end_time at time zone 'EDT' between now() - interval '1 hour' and now()
Questions:
问题：
- Has anyone else run across this before?
- Is there an explanation for this change in behavior? I haven't been able to find anything in the JDBC release notes
- Any advice on how to work around this other than rolling back the driver to an older version?
- 有没有其他人遇到过这个问题？
- 对这种行为变化有解释吗？我在 JDBC 发行说明中找不到任何内容
- 除了将驱动程序回滚到旧版本之外，有关如何解决此问题的任何建议？
Thanks!
谢谢！
回答by JimN
I just ran into this issue myself. I verified that the postgres jdbc driver is indeed picking up the connection timezone from the jvm, and I wasn't able to find a way to override this behavior. It really would be nice if they provided a jdbc url connection parameter for this purpose.
我自己刚刚遇到了这个问题。我验证了 postgres jdbc 驱动程序确实从 jvm 获取连接时区，但我无法找到覆盖此行为的方法。如果他们为此目的提供了 jdbc url 连接参数，那就太好了。
As a workaround, I discovered that my connection pool library (HikariCP) can execute a sql statement for each new connection:
作为一种解决方法，我发现我的连接池库（HikariCP）可以为每个新连接执行一条 sql 语句：
hikariConfig.setConnectionInitSql("set time zone 'UTC'");
回答by Mark Rotteveel
The use of the local timezone as the default timezone is required by the JDBC standard (and API documentation), and is made explicit by PreparedStatement.setTimestamp. However it also applies to all other areas where JDBC sets or retrieves time related data.
JDBC 标准（和 API 文档）要求使用本地时区作为默认时区，并由PreparedStatement.setTimestamp明确说明。然而，它也适用于 JDBC 设置或检索时间相关数据的所有其他领域。
See also my answer to Is java.sql.Timestamp timezone specific?
另请参阅我对java.sql.Timestamp 时区是否特定的回答？
回答by Phas1c
Just in case others struggle with this - you can edit the "conf" file that launches SQLDeveloper and add the following line :
以防万一其他人为此挣扎 - 您可以编辑启动 SQLDeveloper 的“conf”文件并添加以下行：
AddVMOption -Duser.timezone=UTC
I found this file on my machine at :
我在我的机器上找到了这个文件：
/opt/sqldeveloper/ide/bin/ide.conf
Thanks to @a_horse_with_no_namefor pointing me in the right direction with a comment in one of the other answers
感谢@a_horse_with_no_name指出我正确的方向，并在其他答案之一中发表评论
回答by Martin
I don't know JasperReports, but generally speaking, using the JSR-310 (Java 8) date/time types (supported by JDBC 4.2) should work without having to worry about time zone mismatches between database server and client.
我不知道 JasperReports，但一般来说，使用 JSR-310 (Java 8) 日期/时间类型（由 JDBC 4.2 支持）应该可以工作，而不必担心数据库服务器和客户端之间的时区不匹配。
See Using Java 8 Date and Time classesin the PostgreSQL JDBC driver documentation.
请参阅PostgreSQL JDBC 驱动程序文档中的使用 Java 8 日期和时间类。
