postgreSQL 'AT TIME ZONE'，查询范围，'SELECT'所有列(表名.*)表达式

Question

当需要时，我在数据库中使用timestamp(3) with time zone作为表，这在我的情况下几乎是任何情况。

我需要选择一个表的所有列，可能在一个比下面更复杂的查询。

我的问题是如何获得时间戳(带时区)在所需的时区与SQL at time zone '<TIMEZONE>'为一个选择表达式，如q.*其中一个是时间戳(带时区)列。我可能有相同情况的子查询。是否有一个表达式来实现这个查询宽?

SELECT
    q.*, -- created_at timestamp (with time zone) is already in here
    q.created_at AT TIME ZONE 'EET', --instead of this redundant column selection
    u.name AS author,
    u.reputation,
    CASE WHEN count(t.*)=0 THEN '[]' ELSE json_agg(t.*) END as tags
FROM posts q
-- authors
JOIN users u
ON q.author_id = u.id
-- tags
left join post_has_tag p_h_t
on q.id = p_h_t.post_id
left join tags t
on p_h_t.tag_id = t.id
WHERE q.post_type = 'question'
group by q.id, u.id;

Answer 1

如果列的类型为timestamp，则使用AT TIME ZONE是将它们转换为特定时区的正确方法。

但是，不要使用EET。从这个列表中使用一个特定的基于地区的时区，比如Europe/Bucharest——或者任何适合您的时区。

或者，如果列的类型是timestamp with time zone，那么您可以设置会话的时区，postgres将为您完成转换:

SET TIME ZONE 'Europe/Bucharest'

您应该仔细阅读文档，了解这两种时间戳类型的区别。

Answer 2

没有神奇的设置可以将数据类型为timestamptz的所有列转换为timestamp。但是您可以设置所需的目标时区(暂时)来调整timestamptz值的显示 (文本表示):

BEGIN;
SET LOCAL timezone = 'EET';
SELECT q.*, -- including created_at timestamp as original timestamptz type
     , u.name AS author,
     , ...
 -- do something with your data
COMMIT;  -- or ROLLBACK; doesn't matter for just SELECT

这是而不是与应用AT TIME ZONE结构相同，后者实际上将timestamp转换为timestamptz，反之亦然。细节:

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

SET LOCAL的效果持续到事务结束。

并且(就像@Matt已经提到的)，通常(即使稍微贵一点)使用实际的时区名称比使用时区缩写更可取。时区名称考虑了夏令时和时区的其他特性:

SET LOCAL timezone = 'Europe/Istanbul';

• 具有相同属性的时区名称在应用于时间戳
时会产生不同的结果

演示

BEGIN;
SET timezone = 'Europe/Istanbul';
SELECT now();
now
-----------------------------
2016-08-29 22:39:09.275647+03
SET timezone = 'UTC';
SELECT now();
now
-----------------------------
2016-08-29 19:39:09.275647+00
COMMIT;