如何知道 postgresql 8.3 中时间戳的时区 [英] How to know a timezone of a timestamp in postgresql 8.3

问题描述

我使用的是 postgresql 8.3，我想知道特定时间戳(表中的一列)的时区.

I'm using postgresql 8.3 and i would like to know the timezone of a particular timestamp (a column in a table).

在文档中我找到了关键字 "timezone"

In the documentation i've found the keyword "timezone"

但我不明白如何将它应用到表格的列中.可能吗?

But i don't understand how to apply it in a column of table. Is it possible ?

推荐答案

我假设您有一个名为 ct 的列，其类型为 TIMESTAMPTZ 在表 中t.然后你可以使用:

I assume you have a column named ct which has the type TIMESTAMPTZ in the table t. Then you can use:

SELECT EXTRACT(TIMEZONE FROM ct) FROM t;

以秒为单位获取时区的偏移量.它从 UTC/GMT 给你 3600，这意味着 GMT+1，CET 或其他什么.返回的值取决于您的 TIMEZONE 设置.

to get the offset of the timezone in seconds. It that gives you 3600 from UTC/GMT that means either GMT+1, CET or whatever. The value returned depends on your TIMEZONE setting.

示例(我住在德国，实际时区是 GMT+1/CET):

Sample (I live in Germany, actual timezone ist GMT+1/CET):

test=# select '2008-01-01 12:00:00 GMT+5'::timestamptz;
      timestamptz       
------------------------
 2008-01-01 18:00:00+01

test=# set timezone to 'gmt';
SET
test=# select '2008-01-01 12:00:00 GMT+5'::timestamptz;
      timestamptz       
------------------------
 2008-01-01 17:00:00+00

如您所见，它总是在配置的时区输出任何内容.因此，您使用 EXTRACT(TIMEZONE FROM ...) 获得的偏移量取决于您的 TIMEZONE 设置.INSERT 上给出的时区丢失了，因为它不值得保存.重要的是一切都是正确的，不应依赖于 TIMEZONE 设置.PostgreSQL 在这方面做得很好.

As you can see it always outputs anything in the configured timezone. So the offset you will get with EXTRACT(TIMEZONE FROM ...) depends on your TIMEZONE setting. The timezone which was given on INSERT is lost, because it is not worth to be saved. The thing that counts is that everything is correct and that should not depend on the TIMEZONE setting. PostgreSQL does that very well.

这篇关于如何知道 postgresql 8.3 中时间戳的时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！