在“COPY .. TO ..”期间将时间戳转换为给定时区的本地时间

我在 PostgreSQL 数据库中有一个日志表，其中包含event类型列timestamp without time zone.

现在我有一个 bash 脚本，它从日志数据库创建一个 CSV 文件：

...
psql .. -c "COPY (SELECT event, ... FROM logtable order by event desc) TO STDOUT WITH CSV" logdb > log.csv
...

这是在托管数据库的云服务器上执行的，因此 log.csv 中的时间戳字符串采用服务器时区的本地时间。

但是，我喜欢用时间戳字符串来表示我自己的时区的时间。所以我应该能够让 psql 将时间戳 -> 字符串转换为给定的时区。我怎样才能实现这个目标？

首先，你应该使用timestamptz代替timestamp每当与多个时区一起工作时。就可以完全避免这个问题。

Details:

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

You can使用AT TIME ZONE构造像@NuLo建议, it may甚至可以工作，但不完全按照描述。

AT TIME ZONE转换类型timestamp (timestamp without time zone) to timestamptz (timestamp with time zone），反之亦然。这文本表示 of a timestamptz值取决于当前设置您运行该命令的会话中的时区。这两个timestamptz values100% 相同（表示同一时间点）：

'2015-09-02 15:55:00+02'::timestamptz
'2015-09-02 14:55:00+01'::timestamptz

但文本表示是not。显示针对不同时区。如果您获取此字符串文字并将其提供给timestamp类型，时区部分只是ignored你最终会得到不同的价值观。因此，如果你运行你的COPY会话中的语句与原始时区设置相同timestamp值用于建议的操作happens上班。

然而，干净的方法是产生正确的timestamp值首先应用AT TIME ZONE twice:

SELECT event AT TIME ZONE 'my_target_tz' AT TIME ZONE 'my_source_tz', ...
FROM   logtable
ORDER  BY event desc;

'my_target_tz'是“你自己的时区”并且'my_source_tz'示例中云服务器的时区。为了确保遵守 DST，请使用时区名称，不是时区缩写。文档：

例如，时区缩写PST。这样的规格仅仅 定义相对于 UTC 的特定偏移量，与完整时区名称相反 这也可能意味着一组夏令时过渡日期规则。

Related:

• 选择计划项目时考虑 Postgres 中的 DST
• 具有相同属性的时区名称在应用于时间戳时会产生不同的结果

或者，更好的是，使用timestamptz到处都能自动正常工作。