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?
TIA
Ron St.Pierre