当数据类型是带时区和没有时区时,时间戳值在PostgreSQL中存储不同吗?这些差异可以用简单的测试用例来说明吗?
当前回答
下面是一个应该会有帮助的例子。如果您有一个带有时区的时间戳,您可以将该时间戳转换为任何其他时区。如果你没有一个基本时区,它将不会被正确转换。
SELECT now(),
now()::timestamp,
now() AT TIME ZONE 'CST',
now()::timestamp AT TIME ZONE 'CST'
输出:
-[ RECORD 1 ]---------------------------
now | 2018-09-15 17:01:36.399357+03
now | 2018-09-15 17:01:36.399357
timezone | 2018-09-15 08:01:36.399357
timezone | 2018-09-16 02:01:36.399357+03
其他回答
这些差异在PostgreSQL文档中有关于日期/时间类型的说明。是的,TIME或TIMESTAMP的处理在带TIME ZONE或不带TIME ZONE之间是不同的。它不会影响值的存储方式;它影响了它们的解释方式。
时区对这些数据类型的影响在文档中有详细介绍。差异来自于系统可以合理地知道的值:
将时区作为值的一部分,该值可以在客户端中作为本地时间呈现。 如果没有时区作为值的一部分,显然默认时区是UTC,因此它是按照该时区呈现的。
行为的不同取决于至少三个因素:
客户端的时区设置。 值的数据类型(即带时区或不带时区)。 是否使用特定的时区指定该值。
以下是这些因素组合的例子:
foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
timestamp
---------------------
2011-01-01 00:00:00
(1 row)
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
timestamptz
------------------------
2011-01-01 00:00:00+09
(1 row)
foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
timestamp
---------------------
2011-01-01 00:00:00
(1 row)
foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
timestamptz
------------------------
2011-01-01 06:00:00+09
(1 row)
foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
timestamp
---------------------
2011-01-01 00:00:00
(1 row)
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
timestamptz
------------------------
2011-01-01 00:00:00+11
(1 row)
foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
timestamp
---------------------
2011-01-01 00:00:00
(1 row)
foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
timestamptz
------------------------
2011-01-01 08:00:00+11
(1 row)
下面是一个应该会有帮助的例子。如果您有一个带有时区的时间戳,您可以将该时间戳转换为任何其他时区。如果你没有一个基本时区,它将不会被正确转换。
SELECT now(),
now()::timestamp,
now() AT TIME ZONE 'CST',
now()::timestamp AT TIME ZONE 'CST'
输出:
-[ RECORD 1 ]---------------------------
now | 2018-09-15 17:01:36.399357+03
now | 2018-09-15 17:01:36.399357
timezone | 2018-09-15 08:01:36.399357
timezone | 2018-09-16 02:01:36.399357+03
这些区别在PostgreSQL官方文档中有显示。请参考文档进行深入挖掘。
简而言之,TIMESTAMP WITHOUT TIME ZONE不会保存任何与时区相关的信息,如果你给date TIME timezone信息,它只接受date & TIME,忽略timezone
例如
当我保存这个12:13,2021年6月11日IST到PostgreSQL的时间戳没有时区将拒绝时区信息,并保存日期时间12:13,2021年6月11日
但是在TIMESTAMP WITH TIME ZONE的情况下,它以UTC格式保存时区信息。
例如
当我保存这个12:13,2021年6月11日IST到PostgreSQL的时间戳带时区类型变量时,它将把这个时间解释为UTC值和 存储如下所示,UTC 2021年6月11日6:43
注意:UTC + 5.30是IST
在时间转换期间,由TIMESTAMP WITH time ZONE返回的时间将以UTC格式存储,我们可以将其转换为所需的时区,如IST或PST等。
所以PostgreSQL中推荐的时间戳类型是timestamp WITH TIME ZONE或TIMESTAMPZ
我试图比参考的PostgreSQL文档更容易理解地解释它。
TIMESTAMP变量都不存储时区(或偏移量),尽管它们的名称暗示了这一点。区别在于对存储数据的解释(以及预期的应用程序),而不是存储格式本身:
TIMESTAMP WITHOUT TIME ZONE stores local date-time (aka. wall calendar date and wall clock time). Its time zone is unspecified as far as PostgreSQL can tell (though your application may knows what it is). Hence, PostgreSQL does no time zone related conversion on input or output. If the value was entered into the database as '2011-07-01 06:30:30', then no mater in what time zone you display it later, it will still say year 2011, month 07, day 01, 06 hours, 30 minutes, and 30 seconds (in some format). Also, any offset or time zone you specify in the input is ignored by PostgreSQL, so '2011-07-01 06:30:30+00' and '2011-07-01 06:30:30+05' are the same as just '2011-07-01 06:30:30'. For Java developers: it's analogous to java.time.LocalDateTime. TIMESTAMP WITH TIME ZONE stores a point on the UTC time line. How it looks (how many hours, minutes, etc.) depends on your time zone, but it always refers to the same "physical" instant (like the moment of an actual physical event). The input is internally converted to UTC, and that's how it's stored. For that, the offset of the input must be known, so when the input contains no explicit offset or time zone (like '2011-07-01 06:30:30') it's assumed to be in the current time zone of the PostgreSQL session, otherwise the explicitly specified offset or time zone is used (as in '2011-07-01 06:30:30+05'). The output is displayed converted to the current time zone of the PostgreSQL session. For Java developers: It's analogous to java.time.Instant (with lower resolution though), but with JDBC and JPA 2.2 you are supposed to map it to java.time.OffsetDateTime (or to java.util.Date or java.sql.Timestamp of course).
Some say that both TIMESTAMP variations store UTC date-time. Kind of, but it's confusing to put it that way in my opinion. TIMESTAMP WITHOUT TIME ZONE is stored like a TIMESTAMP WITH TIME ZONE, which rendered with UTC time zone happens to give the same year, month, day, hours, minutes, seconds, and microseconds as they are in the local date-time. But it's not meant to represent the point on the time line that the UTC interpretation says, it's just the way the local date-time fields are encoded. (It's some cluster of dots on the time line, as the real time zone is not UTC; we don't know what it is.)
执行以下命令,查看pgAdmin中的diff:
create table public.testts (tz timestamp with time zone, tnz timestamp without time zone);
insert into public.testts values(now(), now());
select * from public.testts;
如果你在Angular / Typescript / Node API / PostgreSql环境中有类似的时间戳精度问题,希望我完整的回答和解决方案能帮助你。
推荐文章
- 使用PSQL命令查找主机名和端口
- Postgresql列表和排序表的大小
- 选择其他表中没有的行
- 在PostgreSQL中使用UTC当前时间作为默认值
- 格式化特定时区的日期
- 优化PostgreSQL进行快速测试
- django test app error -在创建测试数据库时出现错误:创建数据库的权限被拒绝
- Postgres唯一约束与索引
- 使用电子邮件地址为主键?
- 如何保存时区解析日期/时间字符串与strptime()?
- 选择postgres中字段的数据类型
- 定义TypeScript回调类型
- 如何在PostgreSQL中查看视图的CREATE VIEW代码?
- 错误:没有唯一的约束匹配给定的键引用表"bar"
- 如何使用新的PostgreSQL JSON数据类型中的字段进行查询?