Re: Daylight saving time question

----- Original Message -----
From: "Lew" <noone@lwsc.ehost-services.com>
To: <pgsql-general@postgresql.org>
Sent: Saturday, May 23, 2009 2:18 PM
Subject: Re: [GENERAL] Daylight saving time question


> Bayless Kirtley wrote:
>> Thanks Tom and Scott. You got me looking in the right direction. In this
>> case
>> the client and server are on the same machine (testing/development) and
>> psql
>> does return the right result. I tried all the possibilities from the java
>> program,
>> "show timezone", "select current_time" and "select current_timestamp".
>> These
>> were all JDBC queries. When I used result.getString(), the values looked
>> right. When I used result.getTime(), they were off by one hour as if
>> daylight saving were not in effect.
>
> If 'result' is a Java 'java.util.Date' type (or one of its java.sql
> subtypes), then it only holds milliseconds since epoch as if in GMT;
> 'Date' holds no timezone information as such.  In that situation,
> 'result.getTime()' returns a 'long' value.
>
> How exactly are you displaying 'result.getTime()'?  How exactly are you
> determining that its value is "off" by one hour?  Can you show us Java
> code?
>
>> Is this a flaw in the JDBC driver or is that the expected behavior? In
>> either case I do now have a workaround but would like to know.
>
> It is not a flaw in the JDBC driver.
>
> --
> Lew
>
> --

Here is a simplified and tested version of the exact Java code.

    private void run() {
        try {
            Statement stmt = connection.createStatement();
            ResultSet result = stmt.executeQuery("Select CURRENT_TIME");
            result.next();
            String timeStr = result.getString(1);
            System.out.println("String: " + timeStr);
            java.sql.Time time = result.getTime(1);
            System.out.println("Time: " + time);
            java.sql.Time sysTime = new java.sql.Time(new
java.util.Date().getTime());
            System.out.println("Run at:" + sysTime);
        }
        catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

The output from running this code is:

String: 09:17:46.427-05
Time: 08:17:46
Run at: 09:17:46

As you can see it was run at 09:17:46 local daylight saving time today.
Retrieving Postgres time as a String also gives that same value.
Retrieving it as a Time gives a value of one hour earlier. Presumably
it is not recognizing daylight saving time. Since it produces two quite
different values for the same query, I would suggest that this is in fact
a flaw in the JDBC driver, unless, of course, it is actually intended to
behave in that manner for some reason I am not aware of.

As stated earlier, the driver in use is "org.postgresql.Driver" and I
am using PostgreSQL 8.3.1-1, installed about a year ago.

Bayless