Set timezone of PostgreSQL instance

Question

I am using the latest Zend framework to communicate with a PostgreSQL database. Some of my db tables have a now() field that adds the current timestamp. However, the timezone of the db connection can be different for different requests.

Is it possible to set the timezone of a PostgreSQL database on a per-connection basis? I know you can pass driver options to the instance of Zend_Db so I think there lies the trick.

Answer 1

If your connections happen with distinct database roles (logins) that can be tied to a timezone, then PostgreSQL offers a simple solution:

ALTER ROLE my_role SET TIMEZONE = '+1';

Every connection initiated with this role will operate in its preset timezone automatically (unless instructed otherwise).
Note that, quoting the manual:

This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set.

And you may want to use a time zone name instead of the plain offset to follow DST rules and other political manipulation of the local time. More:

• Ignoring timezones altogether in Rails and PostgreSQL

Alternatively, you could create a look-up table for your logins where you store the respective time zones (which might serve additional purposes):

CREATE TABLE usertime(username text primary key, timezone text);
-- careful, "user" is a reserved word (but "timezone" is not).
INSERT INTO usertime VALUES
   ('postgres', '+4')
  ,('my_role' , '+3');

Write a tiny SQL function:

CREATE FUNCTION f_user_ts()
  RETURNS timestamp AS
'SELECT now() AT TIME ZONE u.timezone
 FROM   usertime u
 WHERE  u.username = current_user
' LANGUAGE sql STABLE;

Now, this returns the local timestamp for the current role (user):

SELECT f_user_ts();

More info

See the fine manual for the AT TIME ZONE construct. Both of these syntax variants are valid:

SET TIME ZONE TO 'UTC';
SET TIMEZONE TO 'UTC';

But now() AT TIMEZONE foo; is not! It has to be:

SELECT now() AT TIME ZONE foo;

foo being a text variable (or column like in the function above) holding a time zone offset, abbreviation or name. You can also supply a string literal directly.