Re: TIMEZONE Problem

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.

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.

--
Nigel J. Andrews