postgresql – UTC格式的日期和时间 – 如何在postgres中存储它

https://www.postgresql.org/docs/current/static/datatype-datetime.html

For timestamp with time zone,the internally stored value is always in
UTC (Universal Coordinated Time,traditionally known as Greenwich Mean
Time,GMT). An input value that has an explicit time zone specified is
converted to UTC using the appropriate offset for that time zone. If
no time zone is stated in the input string,then it is assumed to be
in the time zone indicated by the system’s TimeZone parameter,and is
converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output,it is always
converted from UTC to the current timezone zone,and displayed as
local time in that zone. To see the time in another time zone,either
change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

更新了卢卡斯的另一个好点,我不得不提到：

Also in favor of single column is the fact that if you would store
both date and time in separate columns you would still need to combine
them and convert to timestamp if you wanted to change time zone of
date.

不这样做将导致日期’2017-12-31’与时间’23：01：01’在其他时区实际上不仅是不同的时间,而是不同的日期与所有YEAR和MONTH和DAY不同

另一个更新根据Laurenz的通知,不要忘记上面的文档引用
具有指定显式时区的输入值将使用该时区的适当偏移量转换为UTC.这意味着您必须仔细管理输入日期.例如：

t=# create table t(t timestamptz);
CREATE TABLE
t=# set timezone to 'GMT+5';
SET
t=# insert into t select '2017-01-01 00:00:00';
INSERT 0 1
t=# insert into t select '2017-01-01 00:00:00' at time zone 'UTC';
INSERT 0 1
t=# insert into t select '2017-01-01 00:00:00+02';
INSERT 0 1
t=# select * from t;
           t
------------------------
 2017-01-01 00:00:00-05
 2017-01-01 05:00:00-05
 2016-12-31 17:00:00-05
(3 rows)