Skip site navigation (1) Skip section navigation (2)

Issue with the JDBC driver doing timezone conversions on "java.sql.Date"?

From: Fred Janon <fjanon(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Issue with the JDBC driver doing timezone conversions on "java.sql.Date"?
Date: 2009-12-21 06:51:06
Message-ID: 2fd0c7810912202251t77ab60c7hac81d961761e5075@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

Responses

pgsql-jdbc by date

Next:From: Craig RingerDate: 2009-12-21 06:59:58
Subject: Re: Cheapest way to poll for notifications? & Driver improvement question re SSL and notify
Previous:From: Oliver JowettDate: 2009-12-21 03:30:58
Subject: Re: Cheapest way to poll for notifications? & Driver improvement question re SSL and notify

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group