Re: Timezones and time/timestamp values in FE/BE protocol - Mailing list pgsql-hackers

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