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

问题描述

我正在获取我的数据:UTC 日期和时间，在单独的列中以 csv 文件格式.由于我需要将此区域转换为我住的地方的日期和时间，目前在夏天到 UTC+2，也许还有其他一些区域，我想知道在我们谈论的时候在 postgres 中插入数据的最佳做法是什么数据类型.我应该将我的两个数据放在一个列中还是将它们作为类型分开:日期和时间，如果不是，我应该使用时间戳或时间戳z(或其他东西).

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/静态/数据类型-datetime.html

对于带时区的时间戳，内部存储的值始终在UTC(通用协调时间，传统上称为格林威治标准时间)时间，格林威治标准时间).指定了明确时区的输入值是使用该时区的适当偏移量转换为 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屋！