UTC中的日期和时间-如何将它们存储在postgres中？ [英] Date and time in UTC - how to store them in postgres?

问题描述

我正在获取数据：UTC中的日期和时间，格式为单独列中的csv文件格式。由于我需要将此区域转换为我现在居住的地方的日期和时间（当前是夏季，现在是UTC + 2，也许还有其他一些区域），所以我想知道在我们谈论的时候在postgres中插入数据的最佳实践是什么。数据类型。我应该将我的两个数据都放在一列中还是将其分开作为类型：日期和时间，否则应该使用timestamp或timestampz（或其他）。

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).

推荐答案

使用 timestamptz 会将时间戳记存储在UTC中。并会根据其区域设置将其显示给客户端。

use timestamptz it will store your time stamp in UTC. and will display it to the client according to it's locale.

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

对于带时区的时间戳，内部存储的值始终以
UTC（通用协调时间，传统上称为格林威治标准时间
时间，GMT）表示。指定了明确时区的输入值是
，使用该时区的适当偏移量转换为UTC。如果
在输入字符串中未指定时区，则假定它在系统的TimeZone参数指示的时区中为
，并且
使用偏移量转换为UTC。

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.

当输出带有时区值的时间戳时，总是
从UTC转换为当前时区，并显示为
该地区的当地时间。要查看其他时区的时间，请
更改时区或使用AT TIME ZONE构造（请参见第9.9.3节）。

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).

更新，还有 Lukasz 的另一个优点，我不得不提到：

updated with another good point from Lukasz, I had to mention:

也赞成使用单列的事实是，如果您将
的日期和时间都存储在单独的列中，则仍然需要将
合并起来，如果要更改时区，则转换为时间戳记
日期。

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都不一样

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

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

another update As per Laurenz notice, 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:

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)

这篇关于UTC中的日期和时间-如何将它们存储在postgres中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！