PostgreSQL:如何测试时间戳是否有时区

Question

我想防止在PostgreSQL SQL函数中插入任何没有时区的时间戳。我设法想出的唯一方法不起作用，因为Postgres似乎忽略了任何时区字符串后缀，而是使用来自服务器的时区:

hostname:~ username$ date
Tue Sep 27 15:30:16 CEST 2016
hostname:~ username$ uname -a
Darwin hostname.domain 14.5.0 Darwin Kernel Version 14.5.0: Mon Aug 29 21:14:16 PDT 2016; root:xnu-2782.50.6~1/RELEASE_X86_64 x86_64
hostname:~ username$ psql -U postgres
psql (9.5.3)
Type "help" for help.
postgres=# SELECT extract(TIMEZONE FROM '2016-09-19 01:00:00'::timestamptz);
 date_part
-----------
      7200
(1 row)
postgres=# SELECT extract(TIMEZONE FROM '2016-09-19 01:00:00+0200'::timestamptz);
 date_part
-----------
      7200
(1 row)
postgres=# SELECT extract(TIMEZONE FROM '2016-09-19 01:00:00+0800'::timestamptz);
 date_part
-----------
      7200
(1 row)

我有一种感觉，我通常会误解一些事情。有人能帮忙吗?

更新20160928:

为了给出一些解释性的背景，以下是我的数据库(对手头的问题进行了精简):

CREATE DATABASE my_db;
CREATE TABLE my_table
(
    id serial PRIMARY KEY,
    timestamp_utc timestamp with time zone NOT NULL,
    title text NOT NULL
);

，这是我的函数插入数据的当前形式:

CREATE FUNCTION insert_row
(
    p_timestamp_utc timestamp with time zone,
    p_title         text
)
RETURNS integer
LANGUAGE SQL
SECURITY DEFINER
VOLATILE
AS
$BODY$
    INSERT INTO my_table
                (  timestamp_utc    title)
         VALUES (p_timestamp_utc, p_title)
      RETURNING id;
$BODY$;

我创建了一个SQLFiddle;在那里创建函数总是失败…

问题:

即使时间戳参数不带时区，调用函数也成功:

SELECT insert_row('2016-09-01 01:00:00'::timestamptz, 'some title');

，我需要找到一种方法使这个函数调用失败。

为什么我需要使这个失败的原因是我希望任何时间戳所在时区的定义由代码强制执行;另一种选择是，数据库服务器和客户端都以某种方式隐式/静默地同意一个约定——对我来说，这听起来并不像一个明智的举动……

Answer 1

在PostgreSQL中，时区信息不与timestamp with time zone一起存储。相反，时间戳在转换为其内部表示形式时被转换为UTC。

当将timestamp with time zone转换为字符串时，例如显示时，将其转换为TimeZone配置参数中设置的时区。

使用EXTRACT检索时区信息时也会发生同样的情况。文档对此有一点不清楚。

如果你的时区设置如下:

test=> SHOW TimeZone;
   TimeZone
---------------
 Europe/Vienna
(1 row)

你将得到:

test=> SELECT extract(TIMEZONE FROM '2016-01-01 00:00:00-07'::timestamptz);
 date_part
-----------
      3600
(1 row)

因为欧洲中部时间比UTC偏移1小时，而

test=> SELECT extract(TIMEZONE FROM '2016-08-01 00:00:00-07'::timestamptz);
 date_part
-----------
      7200
(1 row)

因为中欧夏令时比UTC偏移2小时