设置PostgreSQL实例的时区 [英] Set timezone of PostgreSQL instance

问题描述

我正在使用最新的Zend框架与PostgreSQL数据库进行通信。我的某些数据库表具有 now（）字段，该字段添加了当前时间戳。但是，对于不同的请求，数据库连接的时区可以不同。

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.

是否可以基于每个连接设置PostgreSQL数据库的时区？我知道您可以将驱动程序选项传递给Zend_Db实例，所以我认为这是个窍门。

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.

推荐答案

，如果您的连接是通过可以绑定到时区的独特的数据库角色（登录）进行的，则PostgreSQL提供了一种简单的解决方案：

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:

这仅在登录时发生；执行 SET ROLE 或 SET SESSION AUTHORIZATION 不会导致设置新的配置值。

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

您可能想使用时区名称而不是普通的偏移量来遵循DST规则和其他对当地时间的政治操纵。更多：

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:

• 完全忽略Rails和PostgreSQL中的时区

• 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');

写一个很小的SQL函数：

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();

更多信息

请参阅精美手册对于 AT TIME ZONE 构造。这两种语法都有效：

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';

但是 now（）在TIMEZONE foo; 不是！它必须是：

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

SELECT now() AT TIME ZONE foo;

foo 是 text 变量（或上面函数中的列），包含时区偏移量，缩写或名称。您也可以直接提供字符串文字。

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.

这篇关于设置PostgreSQL实例的时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！