postgresql UTC 中的日期和时间 - 如何将它们存储在 postgres 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44432682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date and time in UTC - how to store them in postgres?
提问by mycupoftea
I am getting my data: date and time in UTC, in a csv file format in separate columns. Since I will need to convert this zone to date and time of the place where I live, currently in summer to UTC+2, and maybe some other zones I was wondering what is the best practice to insert data in postgres when we are talking about type of data. Should I place both of my data in a single column or keep them separate as types: date and time, and if not should I use timestamp or timestampz (or something else).
我正在获取我的数据：UTC 中的日期和时间，以 csv 文件格式在单独的列中。由于我需要将此区域转换为我居住地的日期和时间，目前在夏季转换为 UTC+2，也许还有其他一些区域，我想知道在我们谈论时在 postgres 中插入数据的最佳实践是什么数据类型。我应该将我的两个数据放在一个列中还是将它们分开作为类型：日期和时间，如果不是，我应该使用时间戳或时间戳（或其他东西）。
回答by Vao Tsun
use timestamptzit will store your time stamp in UTC. and will display it to the client according to it's locale.
使用timestamptz它将以UTC格式存储您的时间戳。并将根据其语言环境将其显示给客户端。
https://www.postgresql.org/docs/current/static/datatype-datetime.html
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).
对于带时区的时间戳，内部存储的值始终采用 UTC（世界协调时间，传统上称为格林威治标准时间，GMT）。使用该时区的适当偏移量将具有指定显式时区的输入值转换为 UTC。如果输入字符串中未指定时区，则假定它位于系统的 TimeZone 参数指示的时区中，并使用时区区域的偏移量转换为 UTC。
当输出带有时区值的时间戳时，它总是从UTC转换为当前时区区域，并显示为该区域的本地时间。要查看另一个时区的时间，请更改时区或使用 AT TIME ZONE 构造（请参阅第 9.9.3 节）。
updatedwith another good point from Lukasz, I had to mention:
更新了Lukasz 的另一个好点，我不得不提到：
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.
同样支持单列的事实是，如果您将日期和时间存储在单独的列中，如果您想更改日期的时区，您仍然需要将它们组合起来并转换为时间戳。
Not doing that would lead to date '2017-12-31' with time '23:01:01' would in other time zone in fact be not only different time, but different date with all YEAR and MONTH and DAY different
不这样做会导致日期 '2017-12-31' 与时间 '23:01:01' 实际上在其他时区不仅时间不同，而且日期不同，所有 YEAR 和 MONTH 和 DAY 都不同
another updateAs per Laurenznotice, don't forget the above docs quote An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. Which means you have to manage the input dates carefully. Eg:
另一个更新根据Laurenz通知，不要忘记上面的文档引用 An input value that has a specific time zone specified is convert to UTC using the appropriate offset for the time zone。这意味着您必须仔细管理输入日期。例如：
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)
