Обсуждение: Inconsistency between timezones
Hi,
I am confused about how the driver interprets (if at all it does) timezones to and from the database. When I run the following queries directly on the database, these are the results I receive:
query: select clock_timestamp() + interval '15 hours'
result: "2009-09-16 06:06:33.801-05"
query: select date_trunc('day', clock_timestamp() + interval '15 hours')
result: "2009-09-16 00:00:00-05"
The above are the values I would expect as well. However, when I run the same two queries (immediately after) from Java (via the JDBC driver), these are the results:
query: select clock_timestamp() + interval '15 hours'
result: "2009-09-16 06:08:44.751"
query: select date_trunc('day', clock_timestamp() + interval '15 hours')
result: "2009-09-15 19:00:00.0"
As you can see, the first query yields the *expected* results. However, the second result seems to have seen some timezone conversion from GMT to Central Time somewhere. I would assume that clock_timestamp() used inside a function, or outside a function should show similar results. Replacing clock_timestamp() with current_timestamp gives the same results. Am I missing something here?
JDBC driver version: 8.1.404
Server version: 8.3
Thanks a bunch,
Manoj
I am confused about how the driver interprets (if at all it does) timezones to and from the database. When I run the following queries directly on the database, these are the results I receive:
query: select clock_timestamp() + interval '15 hours'
result: "2009-09-16 06:06:33.801-05"
query: select date_trunc('day', clock_timestamp() + interval '15 hours')
result: "2009-09-16 00:00:00-05"
The above are the values I would expect as well. However, when I run the same two queries (immediately after) from Java (via the JDBC driver), these are the results:
query: select clock_timestamp() + interval '15 hours'
result: "2009-09-16 06:08:44.751"
query: select date_trunc('day', clock_timestamp() + interval '15 hours')
result: "2009-09-15 19:00:00.0"
As you can see, the first query yields the *expected* results. However, the second result seems to have seen some timezone conversion from GMT to Central Time somewhere. I would assume that clock_timestamp() used inside a function, or outside a function should show similar results. Replacing clock_timestamp() with current_timestamp gives the same results. Am I missing something here?
JDBC driver version: 8.1.404
Server version: 8.3
Thanks a bunch,
Manoj
Manoj Ganesan wrote: > I am confused about how the driver interprets (if at all it does) > timezones to and from the database. > JDBC driver version: 8.1.404 That's a pretty old driver. Try upgrading and see if the problem persists. If you still have problems, then please send through a selfcontained testcase that shows the problem. Timestamp-related problems usually depend a lot on the exact datatypes and queries involved, and the exact JDBC calls that you make. -O