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

Re: Timestamp without Timezone and differing client / server tzs

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Ken Johanson <pg-user(at)kensystem(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Timestamp without Timezone and differing client / server tzs
Date: 2008-07-08 08:06:55
Message-ID: 4873201F.1000700@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Ken Johanson wrote:

> Question: with ts without tz storage, should not a timestamp be 
> normalized to the  servers implicit TZ (UTC in this case), since that is 
> set in the config's client-tz?

If you don't specify a Calendar to setTimestamp, it's assumed you mean 
the client JVM's default timezone (the same timezone that 
Timestamp.toString() assumes). So if you have a Timestamp for 07:09 UTC, 
and your client JVM's default timezone is +0700, then setTimestamp() 
will send "... 14:09 +0700". That'll be stored as 14:09 in a 
timestamp-without-timezone field. (Note that setTimestamp() doesn't know 
if it's setting a with or without timezone parameter, so it always puts 
a timezone in the value and lets the server ignore it where appropriate)

Similarly, if you select a ts-without-timezone value, it's turned into a 
Timestamp representing that time in the client JVM's default timezone.

This behaviour is set up so that you get the intuitively correct results 
  such that e.g. Timestamp.getHours() matches the raw hours value of a 
ts-without-timezone in the DB when you set or get it.

It doesn't really make any sense to use the server's timezone here, 
because all the interpretation/formatting is happening on the client 
side, and the standard Java libs know nothing about the server's timezone.

If you want to interpret a Timestamp as being in a particular timezone, 
use the getTimestamp()/setTimestamp() variants that take an explicit 
Calendar.

-O

In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2008-07-08 10:53:53
Subject: Re: server-side prepared Statements
Previous:From: Oliver JowettDate: 2008-07-08 07:44:08
Subject: Re: Timestamp without Timezone and differing client / server tzs

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