Thread: Daylight saving time question

Bayless Kirtley wrote:
> How can I tell PostgreSQL to use daylight saving time when applicable?
> Times returned by the database are one hour behind.

it uses your client's specified local time zone to determine whether or
not DST is in effect.

    SET TIME ZONE 'America/New York';

or

    SET TIME ZONE 'PST8PDT';

----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "Bayless Kirtley" <bkirt@cox.net>; "PostgreSQL"
<pgsql-general@postgresql.org>
Sent: Sunday, May 17, 2009 10:19 PM
Subject: Re: [GENERAL] Daylight saving time question


> Bayless Kirtley wrote:
>> How can I tell PostgreSQL to use daylight saving time when applicable?
>> Times returned by the database are one hour behind.
>
> it uses your client's specified local time zone to determine whether or
> not DST is in effect.
>
>    SET TIME ZONE 'America/New York';
>
> or
>
>    SET TIME ZONE 'PST8PDT';
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

For some reason I can't seem to make it work. I have tried setting the
timezone
in postgresql.conf as "timezone = 'America/Chicago'" and "timezone =
'CST6CDT'"
both of which still returned one hour behind. I also tried both of your
suggestions
as SQL statements right after establishing a database connection and still
get the
same wrong time.

I have a Java application on Windows XP PRO and the way I am getting the
time is "Select CURRENT_TIME". Is there something I am missing or is there
another way I should be getting the time?

Thanks again

"Bayless Kirtley" <bkirt@cox.net> writes:
> For some reason I can't seem to make it work. I have tried setting the
> timezone
> in postgresql.conf as "timezone = 'America/Chicago'" and "timezone =
> 'CST6CDT'"
> both of which still returned one hour behind. I also tried both of your
> suggestions
> as SQL statements right after establishing a database connection and still
> get the
> same wrong time.

> I have a Java application on Windows XP PRO and the way I am getting the
> time is "Select CURRENT_TIME". Is there something I am missing or is there
> another way I should be getting the time?

Are you sure the system's time is actually set correctly on the server
machine?  Seems like confusion between standard and daylight time in
setting the server's clock might be the underlying issue here.

Another theory is that the database is perfectly fine but there's
something wacko happening on the Java side.  Have you tried running
"select current_time" from some other application, like psql?  (In
that connection I note that "select current_time" only gives time of
day not a full timestamp, so I'd not exactly be surprised if it does
confuse Java.  "select current_timestamp" produces a much less ambiguous
result.)

            regards, tom lane

On Mon, May 18, 2009 at 11:16 AM, Bayless Kirtley <bkirt@cox.net> wrote:
> ----- Original Message ----- From: "John R Pierce" <pierce@hogranch.com>
>> Bayless Kirtley wrote:
>>>
>>> How can I tell PostgreSQL to use daylight saving time when applicable?
>>> Times returned by the database are one hour behind.
>>
>> it uses your client's specified local time zone to determine whether or
>> not DST is in effect.
>>
>>   SET TIME ZONE 'America/New York';
>>
>> or
>>
>>   SET TIME ZONE 'PST8PDT';
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> For some reason I can't seem to make it work. I have tried setting the
> timezone
> in postgresql.conf as "timezone = 'America/Chicago'" and "timezone =
> 'CST6CDT'"
> both of which still returned one hour behind. I also tried both of your
> suggestions
> as SQL statements right after establishing a database connection and still
> get the
> same wrong time.

It's not about what's set in postgresql.conf, it's what the client
timezone is.  If you connect from your client and issue "show
timezone;" what do you get?

> I have a Java application on Windows XP PRO and the way I am getting the
> time is "Select CURRENT_TIME". Is there something I am missing or is there
> another way I should be getting the time?

Nope, sounds right.  Again, what's the client application think the timezone is?

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.

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.

Thanks again.



----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bayless Kirtley" <bkirt@cox.net>
Cc: "John R Pierce" <pierce@hogranch.com>; "PostgreSQL"
<pgsql-general@postgresql.org>
Sent: Monday, May 18, 2009 12:26 PM
Subject: Re: [GENERAL] Daylight saving time question


> "Bayless Kirtley" <bkirt@cox.net> writes:
>> For some reason I can't seem to make it work. I have tried setting the
>> timezone
>> in postgresql.conf as "timezone = 'America/Chicago'" and "timezone =
>> 'CST6CDT'"
>> both of which still returned one hour behind. I also tried both of your
>> suggestions
>> as SQL statements right after establishing a database connection and
>> still
>> get the
>> same wrong time.
>
>> I have a Java application on Windows XP PRO and the way I am getting the
>> time is "Select CURRENT_TIME". Is there something I am missing or is
>> there
>> another way I should be getting the time?
>
> Are you sure the system's time is actually set correctly on the server
> machine?  Seems like confusion between standard and daylight time in
> setting the server's clock might be the underlying issue here.
>
> Another theory is that the database is perfectly fine but there's
> something wacko happening on the Java side.  Have you tried running
> "select current_time" from some other application, like psql?  (In
> that connection I note that "select current_time" only gives time of
> day not a full timestamp, so I'd not exactly be surprised if it does
> confuse Java.  "select current_timestamp" produces a much less ambiguous
> result.)
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

"Bayless Kirtley" <bkirt@cox.net> writes:
> Is this a flaw in the JDBC driver or is that the expected behavior?

You'd be more likely to get the correct answer on pgsql-jdbc.

            regards, tom lane

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bayless Kirtley" <bkirt@cox.net>
Cc: "John R Pierce" <pierce@hogranch.com>; "PostgreSQL"
<pgsql-general@postgresql.org>; <scottmarlowe@gmail.com>
Sent: Monday, May 18, 2009 3:22 PM
Subject: Re: [GENERAL] Daylight saving time question


> "Bayless Kirtley" <bkirt@cox.net> writes:
>> Is this a flaw in the JDBC driver or is that the expected behavior?
>
> You'd be more likely to get the correct answer on pgsql-jdbc.
>
> regards, tom lane

Tom, I'm using org.postgresql.Driver from the jar file
postgresql-8.3-603.jdbc3.jar
that came with my distribution. Is that not the correct one?

Bayless

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

----- 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