设置 PostgreSQL 实例的时区

Question

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

是否可以按每个连接设置PostgreSQL数据库的时区？我知道您可以向Zend_Db实例传递驱动程序选项，因此我认为其中有诀窍。

- Christian-G

---

最好的方法可能是完全避免设置默认时区的需要。相反，确保在处理时间时始终指定时区。例如：使用 @date( 'c' )。这里有一个关于该主题的相关答案。 - Fabien Snauwaert

Answer 1

首先，考虑使用数据类型timestamptz（带有时区的时间戳）而不是timestamp（不带时区的时间戳）。这样用户的时区就不重要了。now()返回timestamptz，你的问题源于将其强制转换为timestamp。请参见：

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

"但我需要timestamp类型！"

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

ALTER ROLE my_role SET TIMEZONE = '+1';

这个角色发起的每个连接都会自动以其预设时区运行（除非另有指示）。
请注意，引用手册：

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

您可能希望使用时区名称而不是纯偏移量来遵循夏令时规则和其他本地时间的政治操纵。更多信息：

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

或者，您可以为登录创建一个查找表，其中存储各自的时区（这可能还具有其他用途）：

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

编写一个小的SQL函数：

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

现在，这将返回当前角色（用户）的本地时间戳：

SELECT f_user_ts();

更多信息

有关AT TIME ZONE结构的详细信息，请参阅精细手册。这两种语法变体都是有效的：

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

但是now() AT TIMEZONE foo;不行！必须是：

SELECT now() AT TIME ZONE foo;

foo 是一个 text 变量（或者像上面的函数一样的列），保存着时区偏移，缩写或名称。你也可以直接提供一个字符串字面量。

- Erwin Brandstetter

---

在Postgres中有事件触发器，但是截至今天，不支持CREATE USER触发器（https://www.postgresql.org/docs/9.3/event-trigger-matrix.html）。这可能会很有用，也许将来会支持。 - Kind Contributor

Answer 2

在 SQL 中，

SET TIMEZONE TO 'America/New_York';

获取时区名称，请使用：

SELECT * 
FROM pg_timezone_names;

- Mike Sherrill 'Cat Recall'

---

这个可以工作，但有没有自动完成的方法？比如，是否有一个PG等效的PDO :: MYSQL_ATTR_INIT_COMMAND =>“SET ...”？ - Christian-G

我不确定我理解如何使用PDO :: MYSQL_ATTR_INIT_COMMAND ...构成“自动”。 每次连接时都不必将其发送到服务器吗？ SQL语句也是如此。 （除非您共享服务器的时区。）但是对于PostgreSQL，您还可以使用PGTZ环境变量。 请参阅上面链接的文档。 - Mike Sherrill 'Cat Recall'

在您的回答中，自动意味着设置一次，然后应用于所有会话，无需进一步显式使用命令 SET TIMEZONE TO 'X'。 - Kind Contributor

@Christian-G，Erwin Brandstetter上面的答案目前得到了更高的投票，可能是你正在寻找的一种“自动化”方式。 - Kind Contributor

Answer 3

这只是一个猜测，但如果您设置使用的角色的时区会怎样呢？

ALTER ROLE my_db_user IN DATABASE my_database
    SET "TimeZone" TO 'UTC';

- Rui Lima