Re: TIMEZONE Problem

On Thu, 1 May 2003, Ron St-Pierre wrote:

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

Ok, so what it amounts to is that you don't use transactions, or all the
statements for your transactions are sent to the DB in one go. Fair enough.

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

I'm thinking that's going to be your problem, or at least a problem. However,
regarding the specific example you gave above, I would say that in order for
you to see the time zone change from ALTER DATABASE ... you would need to
perform your SELECTs in a new session. In addition to the manual a supporting
fact is that current_timestamp is showing you UTC and not the PST you just set
the database to use.

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

But if that someone is in Toronto then PST is no good for them. Isn't that what
you started by saying? If that's so then altering the database timezone isn't
getting you anywhere for two reasons: a) you're not making new connections adn
b) that's going to show times in whatever was the last timezone that the db was
set to. In a multiuser envronment, eg one on Vancouver and one in Toronto, that
can get you wrong displayed timestamps.

I would suggest you try and use the SET TIME ZONE TO 'blah' to change the zone
for the session but throw it into the query string as a prefix to your
query before you submit your queries to your connection pool. That is, instead
of doing the equivalent of:

$connectionPool->execute('SELECT current_timestamp;');

you do:

$connectionPool->execute("SET TIME ZONE TO 'PST'; SELECT current_timestamp;");



--
Nigel J. Andrews