Обсуждение: Issue with the JDBC driver doing timezone conversions on "java.sql.Date"?
I am using postgres server 8.3.7, JDBC driver postgresql-8.4-701.jdbc3.jar
In C:\PostgreSQL\data\postgresql.conf
# CLIENT CONNECTION DEFAULTS
...
timezone = 'GMT'
My application uses Spring and Hibernate and stores java.sql.Date in a table like this:
CREATE TABLE timeson
(
id bigint NOT NULL,
enddatetime timestamp without time zone NOT NULL,
startdatetime timestamp without time zone NOT NULL,
...
}
- My development machine is WinXP. The timezone is GMT+8.
- The Postgres server report GMT as 'show timezone;"
- I am running the Java Web application on the same machine as the Postgres server.
When I check the dates in the database using PGAdmin III on the client, I see the dates in the client timezone (GTM +8).
If I do a
TimeZone.setDefault(TimeZone.getTimeZone("GMT"))
in my application, I see the dates with the "correct" (or expected) GMT timezone.
I don't understand why there is any timezone involved in reading and writing "timestamp without time zone". A quick check in the JDBC source code seems to show that the Java default timezone is used when no timezone is passed in when converting java.sql.Date
ava.sql.Date getDate(int i, java.util.Calendar cal) throws SQLException
{
checkResultSet(i);
if (wasNullFlag)
return null;
if (cal != null)
cal = (Calendar)cal.clone();
return connection.getTimestampUtils().toDate(cal, getString(i));
My goal is to be able to run a postgre server and a client in non GTM timezones and still being able to store java.sql.Date in GMT format, as they are defined in Java.
Any help, insights?
Thanks
Fred
In C:\PostgreSQL\data\postgresql.conf
# CLIENT CONNECTION DEFAULTS
...
timezone = 'GMT'
My application uses Spring and Hibernate and stores java.sql.Date in a table like this:
CREATE TABLE timeson
(
id bigint NOT NULL,
enddatetime timestamp without time zone NOT NULL,
startdatetime timestamp without time zone NOT NULL,
...
}
- My development machine is WinXP. The timezone is GMT+8.
- The Postgres server report GMT as 'show timezone;"
- I am running the Java Web application on the same machine as the Postgres server.
When I check the dates in the database using PGAdmin III on the client, I see the dates in the client timezone (GTM +8).
If I do a
TimeZone.setDefault(TimeZone.getTimeZone("GMT"))
in my application, I see the dates with the "correct" (or expected) GMT timezone.
I don't understand why there is any timezone involved in reading and writing "timestamp without time zone". A quick check in the JDBC source code seems to show that the Java default timezone is used when no timezone is passed in when converting java.sql.Date
ava.sql.Date getDate(int i, java.util.Calendar cal) throws SQLException
{
checkResultSet(i);
if (wasNullFlag)
return null;
if (cal != null)
cal = (Calendar)cal.clone();
return connection.getTimestampUtils().toDate(cal, getString(i));
My goal is to be able to run a postgre server and a client in non GTM timezones and still being able to store java.sql.Date in GMT format, as they are defined in Java.
Any help, insights?
Thanks
Fred
Re: Issue with the JDBC driver doing timezone conversions on "java.sql.Date"?
От
Oliver Jowett
Дата:
Fred Janon wrote:
> When I check the dates in the database using PGAdmin III on the client,
> I see the dates in the client timezone (GTM +8).
> If I do a
> TimeZone.setDefault(TimeZone.getTimeZone("GMT"))
> in my application, I see the dates with the "correct" (or expected) GMT
> timezone.
>
> I don't understand why there is any timezone involved in reading and
> writing "timestamp without time zone".
Because "timestamp without time zone" is a little counterintuitive. It
represents a particular wallclock date/time; it does not represent a
unique instant in time.
It's roughly like storing the string "2009-12-21 21:00:00" - that time
interpreted in my timezone is right now, but it'll be some different
instant when interpreted in your timezone.
So when the JDBC driver needs to convert between a Java Timestamp -
which *does* represent a particular instant in time, in terms of
milliseconds-since-the-epoch - and a timestamp-without-timezone, it has
to pick a particular timezone to do that. The driver follows the JDBC
spec and uses the JVM's default timezone unless you explictly provide a
Calendar to use when calling getTimestamp()/setTimestamp().
If you're trying to store particular instants-in-time, you probably want
to use TIMESTAMP WITH TIME ZONE (which, again a little
counterintuitively, does NOT actually store a timezone - it just stores
milliseconds-since-the-epoch and converts that to/from different
timezones on demand)
-O