I'm working on a problem in the JDBC driver that's related to
timezones.

How does PostgreSQL handle timezones in the FE/BE protocol
exactly?

When a client sends a time or timestamp value to the server via
the FE/BE protocol, should that be:
1) a value in the client's timezone?
2) a value in the server's timezone?
3) a value in a common frame of reference (GMT/UTC)?
4) any value with an explicit timezone?

And how should a time or timestamp value returned by the server
be interpreted in the client interface?

And how does this all depend on the timezone setting of the
server?

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Sun, 09 Sep 2001 20:28:15 +0200
Rene Pijlman <rene@lab.applinet.nl>
Timezones and time/timestamp values in FE/BE protocol
Rene,

Since the FE/BE protocol deals only with string representations of 
values, the protocol doesn't have too much to do with it directly.  It 
is what happens on the client and server sides that is important here.

Under the covers the server stores all timestamp values as GMT.  When a 
select statement queries one the value is converted to the session 
timezone and formated to a string that includes the timezone offset used 
  (i.e. 2001-09-09 14:24:35.12-08 which the database had stored as 
2001-09-09 22:24:35.12 GMT).  The client then needs to handle this 
accordingly and convert to a different timezone if desired.

On an insert or update the client and server are essentially doing the 
opposite.  The client converts the timestamp value to a string and then 
the server converts that string to GMT for storage.  If the client does 
not pass the timezone offset (i.e. 2001-09-09 14:24:35.12 instead of 
2001-09-09 14:24:35.12-08) then the server needs to guess the timezone 
and will use the session timezone.

Now when it comes to the JDBC code this is what happens.  (Since you 
didn't state what specific problem you where having I will give a 
general overview).

When the JDBC driver connects to the server it does one thing timestamp 
related.  It does a 'set datestyle to "ISO"' so that the client and the 
server both know how the strings are formated.

I don't know what the session timezone defaults to, but it really 
shouldn't matter since the server always sends the timezone offset as 
part of the string representation of the timestamp value.  Therefore the 
JDBC client can always figure out how to convert the string to a Java 
Timestamp object.

On the insert/update opperation the JDBC client converts the Timestamp 
object to GMT (see the logic in setTimestamp() of PreparedStatement) and 
then builds the string to send to the server as the formated date/time 
plus the timezone offset used (GMT in this case).  Thus it does 
something that looks like:  "2001-09-09 14:24:35.12" + "+00".  When the 
server gets this string it has all the information it needs to convert 
to GMT for storage (it actually doesn't need to do anything since the 
value is clearly already in GMT).

I hope this helps to answer your questions.  If you could post a bit 
more about the issue you are having I might be able to be more specific.

thanks,
--Barry



Rene Pijlman wrote:
> I'm working on a problem in the JDBC driver that's related to
> timezones.
> 
> How does PostgreSQL handle timezones in the FE/BE protocol
> exactly?
> 
> When a client sends a time or timestamp value to the server via
> the FE/BE protocol, should that be:
> 1) a value in the client's timezone?
> 2) a value in the server's timezone?
> 3) a value in a common frame of reference (GMT/UTC)?
> 4) any value with an explicit timezone?
> 
> And how should a time or timestamp value returned by the server
> be interpreted in the client interface?
> 
> And how does this all depend on the timezone setting of the
> server?
> 
> Regards,
> Renï¿½ Pijlman <rene@lab.applinet.nl>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 
> 



Sun, 09 Sep 2001 13:38:52 -0700
Barry Lind <barry@xythos.com>
Re: Timezones and time/timestamp values in FE/BE protocol
On Sun, 09 Sep 2001 13:38:52 -0700, you wrote:
[...]
Thanks for your explanation. This helps a lot.

>If you could post a bit more about the issue you are having 
>I might be able to be more specific.

I'm looking at the 4 remaining failures of our own JDBC test
suite. They all have to do with timestamps and times, and they
are all caused by a 1 hour shift between the expected value and
the actual value. I run both the backend and the JVM on the same
Linux test server. 

Its located in Amsterdam, The Netherlands, Central European
Daylight Savings Time (CETDST, UTC+2, GMT+2). I always thought I
was in CET=GMT+1, but now the offset is 2, because of daylight
saving time (whoever invented that should be #!$^&). Perhaps I
should go live in Greenwich, they don't seem to have daylight
saving time overthere.

In psql I see: 
    show timezone;
    NOTICE:  Time zone is unset

Here is some detailed information about the failures. I'm
refering to line numbers in 7.2 current CVS:
TimeTest.java       revision 1.1
TimestampTest.java  revision 1.2

1) TimeTest.java:89

getHours(t) expected 1, actual 0
t.toString() returns the expected "01:02:03", but this is
because java.sql.Time.toString() converts to the JVM's timezone.

2) TimeTest.java:96

getHours(t) expected 23, actual 0
t.toString returns "00:59:59"

3) TimestampTest.java:115

Expected: getTimestamp(1970,6,2,8,13,0) returns "1970-06-02
08:13:00.0"
Actual: t.toString() returns "1970-06-02 09:13:00.0"

4) TimestampTest.java:115 (second time around)

Expected: getTimestamp(1970,6,2,8,13,0) returns "1970-06-02
08:13:00.0"
Actual: t.toString() returns "1970-06-02 07:13:00.0"

My first impression is that in all cases a timezone shift is
applied in only one direction (store vs. retrieve). The cause
might also be a problem with daylight saving time, there are
some comments about that in TimestampTest.java.

Up till now I've managed without a graphical debugger, but to
get a good feel for what's happening between the test code and
the wire I think it'll be easier to setup JBuilder with the
driver and step through the code.

But now its almost bedtime in my timezone, and you never know
with these mailing lists. Sometimes the solution is in your
inbox when you wake up :-)

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Sun, 09 Sep 2001 23:43:36 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: Timezones and time/timestamp values in FE/BE protocol
On Sun, 09 Sep 2001 23:43:36 +0200, I wrote:
>I'm looking at the 4 remaining failures of our own JDBC test
>suite. They all have to do with timestamps and times

FYI, Liam mailed me that he will soon post a patch for this.

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Wed, 12 Sep 2001 23:41:39 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: Timezones and time/timestamp values in FE/BE protocol