PostgreSQL 9.2 JDBC 驱动程序使用客户端时区? [英] PostgreSQL 9.2 JDBC driver uses client time zone?

问题描述

我在使用 PostgreSQL 数据库和 PostgreSQL JDBC 驱动程序时遇到了一个有趣的挑战.似乎最新版本的驱动程序 9.2 在执行日期/时间匹配时使用 客户端 时区.

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 client time zone when performing date/time matches.

当服务器 (JasperReports Server) 设置为 UTC 并且数据库服务器设置为 US/Eastern 时，这就会成为一个问题.

This becomes a problem when the server (JasperReports Server) is set to UTC and the database server is set to US/Eastern.

如果我从设置为 UTC 时区的客户端运行以下查询，我会使用 9.0 JDBC 驱动程序和 9.2 JDBC 驱动程序得到不同的结果.

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.

select now(), extract(timezone FROM now()), current_setting('TIMEZONE'), now()-interval '1 hour' as "1HourAgo"

使用 9.0 JDBC 驱动程序的结果:

Results using 9.0 JDBC driver:

now                         date_part   current_setting     1HourAgo
2013-08-26 15:33:57.590089  -14,400     US/Eastern          2013-08-26 14:33:57.590089

使用 9.2 JDBC 驱动程序的结果:

Results using 9.2 JDBC driver:

now                         date_part   current_setting     1HourAgo
2013-08-26 15:41:49.067903  0           UTC                 2013-08-26 14:41:49.067903

这会导致查询中的 WHERE 语句返回不正确的结果.例如，

This is causing a WHERE statement in a query to return incorrect results. For example,

WHERE end_time between now() - interval '1 hour' and now()

使用 9,0 驱动程序按预期工作，但使用 9,2 驱动程序不返回任何结果，因为驱动程序似乎正在偏移 end_time 的值以匹配 UTC(客户端的时区).以下是一种解决方法，但很丑陋:

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:

WHERE end_time at time zone 'EDT' between now() - interval '1 hour' and now()

问题:

1. 有没有其他人遇到过这种情况?
2. 对这种行为变化有什么解释吗?我在 JDBC 发行说明中找不到任何内容
3. 除了将驱动程序回滚到旧版本之外，还有其他关于如何解决此问题的建议吗?

谢谢！

推荐答案

我自己刚刚遇到了这个问题.我验证了 postgres jdbc 驱动程序确实从 jvm 获取连接时区，但我无法找到覆盖此行为的方法.如果他们为此提供了 jdbc url 连接参数，那就太好了.

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.

作为一种解决方法，我发现我的连接池库(HikariCP)可以为每个新连接执行一个 sql 语句:

As a workaround, I discovered that my connection pool library (HikariCP) can execute a sql statement for each new connection:

hikariConfig.setConnectionInitSql("set time zone 'UTC'");

这篇关于PostgreSQL 9.2 JDBC 驱动程序使用客户端时区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！