具有原始timestamptz的时区date_trunc

Question

我有一个timestamptz，我想要date_trunc，所以它删除小时数：

但是，因为date_trunc删除了时区，所以我需要这样做：

date_trunc('day', '2019-01-01T17:43-03'::timestamptz) at time zone '-03'

但是，我不想硬编码时区，因为查询是在许多不同的时区中使用timestamptz运行的（这些时区是输入到查询的，而不是存储的）。所以我希望时区从原始时间戳中提取出来。我试着做这样的事情，但不起作用：

date_trunc('day', '2019-01-01T17:43-03'::timestamptz) at time zone EXTRACT(...)

与此相关，我试图从timestamptz中提取时区，但只得到0。

SELECT EXTRACT(timezone FROM TIMESTAMP WITH TIME ZONE '2019-01-01T00:00+03')
0

Answer 1

我相信您可能对PostgreSQL中时间戳和时区的存储方式有误解（因为您似乎希望在调用date_trunc时保留-03并且“希望从原始时间戳中提取时区”）。根据文件：

当输出具有时区值的时间戳时，它总是从UTC转换为当前时区区域，并在该区域中显示为本地时间。要查看另一个时区的时间，可以更改时区或使用AT时区构造（请参见第9.9.3节）。

因此，“在许多不同的时区中具有时间戳的不同客户机”的语句如果为真，将全部转换为UTC进行存储，然后显示在本地时区（或您指定的时区）中进行输出。因此，调用date_trunc()实际上会截断UTC时间戳，如果希望它显示在特定的时区中，则需要添加at TIME zone子句。

更新：这里有一个示例：

edb=# select date_trunc('day', '2019-01-01T17:43-03'::timestamptz) ;
       date_trunc       
------------------------
 2019-01-01 00:00:00+00
(1 row)

edb=# set timezone to 'US/Pacific';
SET
edb=# select date_trunc('day', '2019-01-01T17:43-03'::timestamptz) ;
       date_trunc       
------------------------
 2019-01-01 00:00:00-08
(1 row)

如您所见，date_trunc将追加我定义的时区-它不会被省略。