设置PostgreSQL实例的时区

Question

我使用最新的Zend框架与PostgreSQL数据库通信。我的一些db表有一个now()字段，用于添加当前时间戳。但是，对于不同的请求，db连接的时区可以不同。

是否可以在每个连接的基础上设置PostgreSQL数据库的时区?我知道你可以将驱动程序选项传递给Zend_Db的实例，所以我认为这就是诀窍。

Answer 1

首先考虑数据类型timestamptz (timestamp with time zone)而不是timestamp (timestamp without time zone)。那么用户所在的时区就不重要了。now()返回timestamptz，你的问题源于强制timestamp。看到:

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

"但我需要的类型是timestamp "

如果您的连接发生在数据库角色(登录)上，可以绑定到时区，那么PostgreSQL提供了一个简单的解决方案:

ALTER ROLE my_role SET TIMEZONE = '+1';

此角色初始化的每个连接将在其预设的时区自动操作(除非另有指示)。
注意，引用手册:

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

并且您可能希望使用时区名称而不是普通偏移量，以遵循DST规则和其他对本地时间的政治操作。:

• 在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变量(或像上面函数中的列)，包含时区偏移量、缩写或名称。您也可以直接提供字符串字面值

Answer 2

In SQL，

SET TIMEZONE TO 'America/New_York';

要获取时区名称，请使用

SELECT * 
FROM pg_timezone_names;

Answer 3

只是一个很小的机会，但是如果您设置了正在使用的角色的时区呢?

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