在“ COPY .. TO ..”期间将时间戳转换为给定时区的本地时间 [英] Transform timestamp to local time for a given timezone during 'COPY .. TO ..'

问题描述

我在PostgreSQL数据库中有一个日志表，其中的 event 列的类型为 timestamp，没有时区。 / p>

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

  ... 
 psql .. -c复制（选择事件，...从按事件desc的日志表顺序）到使用CSV的标准输出 logdb> log.csv 
 ... 
  

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

但是，我希望使用时间戳字符串来表示我自己所在时区的时间。因此，我将能够让psql将timestamp->字符串转换为给定的时区。我该如何实现？

解决方案

首先，您应该使用 timestamptz 而不是 timestamp 。

详细信息：

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

您可以使用 AT TIME ZONE 构造，如 @NuLo建议，它甚至可能奏效，但与描述不完全相同。

AT时区转换类型 timestamp （不带时区的时间戳）到 timestamptz （带时区的时间戳），反之亦然。 timestamptz 值的 文本表示 取决于时区的当前设置在运行命令的会话中。这两个 timestamptz 值是100％相同的（表示相同的时间点）：

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

但是文本表示形式不是 。该显示适用于不同时区。如果您采用此字符串文字并将其提供给 timestamp 类型，则时区部分只是忽略，而最终会以 值。因此，如果您在具有与原始 timestamp 值相同的时区设置的会话中运行 COPY 语句，建议的操作会发生。

干净的方法是产生正确的时间戳值的开头是应用时区 两次 ：

 选择事件在时区'my_target_tz'在时区'my_source_tz'，... 
从日志表
 ORDER BY事件描述；  

'my_target_tz'是您的自己的时区和示例中的'my_source_tz'云服务器的时区。为确保遵守DST，请使用时区名称，而不要使用时区缩写。 文档：

时区缩写，例如 PST 。这样的规范仅
定义了与UTC的特定偏移量，而全时区名称
则可能暗示着一组夏令时过渡日期规则。

相关：

• 选择Postgres中的DST时（选择预定项目时）


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

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

I have a log table in a PostgreSQL database with an event column of type timestamp without time zone.

Now I have a bash script, which creates a CSV file from the log database:

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

This is executed on the cloud server on which the DB is hosted and therefore, the timestamp strings in log.csv are in local time of the timezone of the server.

However, I like to have the timestamp strings to represent the time of my own time zone. So I shall be able to let psql transform the timestamp -> string to a given timezone. How can I achieve this?

解决方案

First of all, you should use timestamptz instead of timestamp whenever working with multiple times zones. Would avoid the problem completely.

Details:

• Ignoring timezones altogether in Rails and PostgreSQL

You can use the AT TIME ZONE construct like @NuLo suggests, it may even work, but not exactly as described.

AT TIME ZONE converts the type timestamp (timestamp without time zone) to timestamptz (timestamp with time zone) and vice versa. The text representation of a timestamptz value depends on the current setting of the time zone in the session in which you run the command. These two timestamptz values are 100 % identical (denote the same point in time):

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

But the text representation is not. The display is for different time zones. If you take this string literal and feed it to a timestamp type, the time zone part is just ignored and you end up with different values. Hence, if you run your COPY statement in a session with the same time zone setting as your original timestamp values are for, the suggested operation happens to work.

The clean way, however, is to produce correct timestamp values to begin with by applying 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' is "your own time zone" and 'my_source_tz' the time zone of the of the cloud server in the example. To make sure that DST is respected use time zone names, not time zone abbreviations. The documentation:

A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well.

Related:

• Accounting for DST in Postgres, when selecting scheduled items
• Time zone names with identical properties yield different result when applied to timestamp

Or, much better yet, use timestamptz everywhere and it works correctly automatically.

这篇关于在“ COPY .. TO ..”期间将时间戳转换为给定时区的本地时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！