PostgreSQL 9.2 JDBC驱动程序使用客户端时区吗? [英] PostgreSQL 9.2 JDBC driver uses client time zone?
问题描述
在使用带有PostgreSQL JDBC驱动程序的PostgreSQL数据库时,我遇到了一个有趣的挑战.似乎最新版本的驱动程序9.2在执行日期/时间匹配时会使用 client 时区.
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()
问题:
- 以前有没有其他人遇到过这个问题?
- 是否有这种行为变化的解释?在JDBC发行说明中我找不到任何东西
- 除了将驱动程序回滚到旧版本以外,是否还有其他解决方法的建议?
谢谢!
推荐答案
我本人只是遇到了这个问题.我验证了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屋!