Re: TIMEZONE Problem - Mailing list pgsql-general

Nigel J. Andrews wrote:

 >On Thu, 1 May 2003, Ron St-Pierre wrote:
 >
 >>I've got multiple postgres databases set up to store data for some java
 >>web applications. The databases and apps span multiple time zones, but a
 >>person in a particular time zone will access the app / database
 >>corresponding to their time zone. I want generic queries to display the
 >>local time as some of the data is time sensitive.
 >>
 >>There is one instance of postmaster running (v 7.3 on RedHat), and we're
 >>using java connection pooling to access all databases. When postgres was
 >>configured, no timezone was configured. However, I've changed the
 >>timezone for each database using the ALTER DATABASE bc SET TIME ZONE
 >>'PST'; command, changing database and time zone as appropriate. SHOW ALL
 >>(and other commands) verify that the time zone is set up properly. When
 >>I run a SELECT CURRENT_TIMESTAMP; I get
 >>           timestamptz
 >>-------------------------------
 >>  2003-05-01 20:26:54.634211+00
 >>
 >>and SELECT LOCALTIMESTAMP; returns
 >>          timestamp
 >>----------------------------
 >>  2003-05-01 20:27:22.564965
 >>
 >>In both cases the time is returned as UTC. Is there any way I can
 >>configure the databases to return local times/dates? These apps are
 >>already built and I want to avoid changing all of the coded queries. Can
 >>anyone help?
 >>
 >
 >No, but why would doing this help with your multiple time zones for
display?
 >
 >What you need is for the client to issue a SET TIME ZONE TO 'blah';
when the
 >connection is made. Presumably that would be transmitted through the
connection
 >pool and so the client will see things as expected, even if not in the
same
 >zone as the last client on the connection allocated to it from the pool.
 >

I've tried the SET TIME ZONE TO 'blah'; approach, but it applies to the
current session only.

 >
 >
 >That, of course, assumes one connection is allocated to a client for the
 >duration of one http transaction (or whatever is the equivalent). If the
 >connection pooling is horrible and allocates each statement to a random
 >connection then you're stuffed, unless you issue multiple statements
in one go
 >to the DB.
 >

Actually, the 'horrible' connection pooling works exactly as intended,
allocating each statement to a random connection. We've allocated a
certain number of connections which are shared amongst the various
databases, and it is working out quite well.

All of the databases and apps are set up identically, and schema /
program changes to one are rolled out to all the others. All time
related columns are set up as timestamp without time zone. Some of the
time sensitive information includes the closing time of when bid
proposals will be accepted, so someone in Vancouver, for example, would
be told that bids will be accepted until 14:00:00 PST on a particular
date. This is a requirement from our client, so we're can't just tell
them that all times are expressed as UTC and have them do the time
differences themselves.

Ron