7

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Christian-G
  • 2,371
  • 4
  • 27
  • 47
  • It may be preferable to just avoid the need to set a default timezone altogether. Instead, make sure you specify a timezone whenever you work with time. e.g.: using `@date( 'c' )`. Here's a [related answer on that topic](https://stackoverflow.com/a/46060039/1717535). – Fabien Snauwaert Sep 05 '17 at 17:20

3 Answers3

9

First, consider the data type timestamptz (timestamp with time zone) instead of timestamp (timestamp without time zone). Then the time zone of users does not matter. now() returns timestamptz, your problem stems from coercing that to timestamp. See:

"But I need the type timestamp!"

If your connections happen with 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:

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  -- careful, "user" is a reserved word
, timezone text              -- ... 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
  LANGUAGE sql STABLE AS
$func$
SELECT now() AT TIME ZONE u.timezone
FROM   usertime u
WHERE  u.username = current_user
$func$;

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • In Postgres there are Event Triggers, but as of today `CREATE USER` trigger are not supported (https://www.postgresql.org/docs/9.3/event-trigger-matrix.html). That would have been useful, maybe in the future. – Kind Contributor Aug 23 '20 at 08:32
6

In SQL, 

SET TIMEZONE TO 'America/New_York';

To get the names of the time zones, use

SELECT * 
FROM pg_timezone_names;
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 1
    This works, but is there a way to do this automatically? Like, is there a PG equivalent of PDO::MYSQL_ATTR_INIT_COMMAND => "SET ..." – Christian-G Sep 19 '11 at 22:02
  • I'm not sure I understand how using PDO::MYSQL_ATTR_INIT_COMMAND... constitutes "automatic". Don't you have to send that to the server each time you connect? Same with that SQL statement. (Unless you share the server's timezone.) But with PostgreSQL you can also use the PGTZ environment variable. See the documentation linked above. – Mike Sherrill 'Cat Recall' Sep 19 '11 at 22:18
  • In the context of your answer, automatic means set once, then applied to all sessions without needing further explicit use of the command `SET TIMEZONE TO 'X'` – Kind Contributor Aug 23 '20 at 08:27
  • @Christian-G the answer from Erwin Brandstetter above and currently voted higher is probably one kind of "automatic" that you are looking for. – Kind Contributor Aug 23 '20 at 08:29
1

just a long shot, but what if you set the timezone of the role you are using?

ALTER ROLE my_db_user IN DATABASE my_database
    SET "TimeZone" TO 'UTC';
Rui Lima
  • 7,185
  • 4
  • 31
  • 42