PostgreSQL/JDBC 和 TIMESTAMP 与 TIMESTAMPTZ
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22922155/
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
PostgreSQL/JDBC and TIMESTAMP vs. TIMESTAMPTZ
提问by robert_difalco
I've been going through a lot of pain dealing with Timestamps lately with JPA. I have found that a lot of my issues have been cleared up by using TIMESTAMPTZ for my fields instead of TIMESTAMP. My server is in UTC while my JVM is in PST. It seems almost impossible with JPA to normalize on UTC values in the database when using TIMESTAMP WITHOUT TIMEZONE.
我最近在处理 JPA 的时间戳时经历了很多痛苦。我发现我的很多问题已经通过对我的字段使用 TIMESTAMPTZ 而不是 TIMESTAMP 来解决。我的服务器使用 UTC 而我的 JVM 使用 PST。使用 TIMESTAMP WITHOUT TIMEZONE 时，JPA 似乎几乎不可能对数据库中的 UTC 值进行规范化。
For me I use these fields for stuff like "when was the user created", "when did they last use their device", "when was the last time they got an alert", etc. These are typically events so they are instance in time sorts of values. And because they will now by TIMESTAMPTZ I can always query them for a particular zone if I don't want them UTC.
对我来说，我将这些字段用于诸如“用户何时创建”、“他们上次使用他们的设备是什么时候”、“他们上次收到警报是什么时候”等内容。这些通常是事件，因此它们是实例时间排序值。并且因为他们现在将通过 TIMESTAMPTZ，如果我不想要他们的 UTC，我总是可以查询他们的特定区域。
So my question is, for a Java/JPA/PostgreSQL server, when WOULD I want to use TIMESTAMP over TIMESTAMPTZ? What are the use cases for it? Right now I have a hard time seeing why I'd ever want to use TIMESTAMP and because of that I'm concerned that I'm not grasping its value.
所以我的问题是，对于 Java/JPA/PostgreSQL 服务器，我什么时候想使用 TIMESTAMP 而不是 TIMESTAMPTZ？它有哪些用例？现在我很难理解为什么我想要使用 TIMESTAMP，因此我担心我没有掌握它的价值。
采纳答案by JB Nizet
You could use it to represent what Joda-Time and the new Java 8 time APIs call a LocalDateTime. A LocalDateTimedoesn't represent a precise point on the timeline. It's just a set of fields, from year to nanoseconds. It is "a description of the date, as used for birthdays, combined with the local time as seen on a wall clock".
您可以使用它来表示 Joda-Time 和新的 Java 8 时间 API 调用的LocalDateTime. ALocalDateTime不代表时间线上的精确点。它只是一组字段，从年份到纳秒。它是“对日期的描述，用于生日，结合挂钟上的当地时间”。
You could use it to represent, for example, the fact that your precise birth date is 1975-07-19 at 6 PM. Or that, all across the world, the next new year is celebrated on 2015-01-01 at 00:00.
例如，您可以使用它来表示您的确切出生日期是 1975-07-19 下午 6 点这一事实。或者，在世界各地，下一个新年在 2015-01-01 的 00:00 庆祝。
To represent precise moments, like the moment Armstrong walked on the moon, a timestamp with timezone is indeed more appropriate. Regardless of the timezone of the JVM and the timezone of the database, it should return you the correct moment.
为了表示精确的时刻，比如阿姆斯特朗在月球上行走的时刻，带时区的时间戳确实更合适。无论 JVM 的时区和数据库的时区如何，它都应该为您返回正确的时刻。
回答by Basil Bourque
Generally use TIMESTAMPTZ
一般使用TIMESTAMPTZ
Here's advice from David E. Wheeler, a Postgres expert, in a blog post whose title says it all:
Always Use TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)
以下是 Postgres 专家 David E. Wheeler 在一篇博文中的建议，其标题说明了一切：
Always Use TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)
If you are tracking actual moments, specific points on the timeline, use TIMESTAMP WITH TIME ZONE.
如果您要跟踪实际时刻、时间线上的特定点，请使用TIMESTAMP WITH TIME ZONE.
One Exception: Partitioning
一个例外：分区
Wheeler's sole exception is when partitioning on timestamps, because of technical limitations. A rare exceptionfor most of us.
由于技术限制，Wheeler 唯一的例外是按时间戳进行分区。对于我们大多数人来说，这是一个罕见的例外。
For information about partitioning, see docand see the Wiki.
Misnomer
用词不当
The data types names timestamp with time zoneand timestamp without time zoneare misnomers. In bothcases the date-time value is stored in UTC (no time zone offset). Read that previous sentence again. UTC, always.The "with time zone" phrase means "with attention paid to time zone", not "store the time zone alongside this value". The difference between the types is whether any time zone should be applied either during storage (INSERT or UPDATE) or retrieval (SELECT query). (This behavior is described for Postgres -- Other databases vary widelyin this regard.)
数据类型名称timestamp with time zone和timestamp without time zone名称用词不当。在这两种情况下，日期时间值都以 UTC 格式存储（无时区偏移）。再读一遍上一句。UTC，始终。“with time zone”短语的意思是“注意时区”，而不是“在这个值旁边存储时区”。类型之间的区别在于是否应在存储（INSERT 或 UPDATE）或检索（SELECT 查询）期间应用任何时区。（此行为针对 Postgres 进行了描述——其他数据库在这方面差异很大。）
More precisely, one should say that TIMESTAMP WITHOUT TIME ZONE stores date-time values with no time zone. But without any time frame reference, anyone looking at that data would have to assume (hope, pray?) that the values are UTC. But again, moot as you should almost never use this type.
更准确地说，应该说 TIMESTAMP WITHOUT TIME ZONE 存储没有时区的日期时间值。但是如果没有任何时间框架参考，任何查看该数据的人都必须假设（希望，祈祷？）这些值是 UTC。但同样，没有实际意义，因为您几乎不应该使用这种类型。
Read the doccarefully, and experiment a bit to clarify your understanding.
仔细阅读文档，并进行一些实验以阐明您的理解。
Unzoned
未分区
If you want to store the general idea of a possible time rather than a specific moment, use the other type, TIMESTAMP WITHOUT TIME ZONE.
如果您想存储可能时间的一般概念而不是特定时刻，请使用其他类型，TIMESTAMP WITHOUT TIME ZONE.
For example, Christmas starts this year at the first moment of December 25th, 2017. That would be 2017-12-25T
00:00:00with no indicator of time zone nor offset-from-UTC. This value is only a vague idea about possible moments. It has no meaning until we apply a time zone (or offset). So we store this using TIMESTAMP WITHOUT TIME ZONE.
例如，今年圣诞节从 2017 年 12 月 25 日的第一个时刻开始。那将2017-12-25T
00:00:00没有时区指标，也没有与 UTC 的偏移量。这个值只是关于可能时刻的一个模糊概念。在我们应用时区（或偏移量）之前，它没有任何意义。所以我们使用TIMESTAMP WITHOUT TIME ZONE.
The elves staffing Santa's Special Events Logistics Departmentapply the time zones as part of their planning process. The earliest time zone is currently Pacific/Kiribati, 14 hours ahead of UTC. The elves schedule Santa's first arrival there. The elves schedule a flight plan taking the reindeer on to other time zones where midnight comes shortly after, such as Pacific/Auckland. They continue going westward as each zone's midnight arrives. Hours later in Asia/Kolkata, still later in Europe/Paris, still more hours later in America/Montrealand so on.
圣诞老人特别活动后勤部门的精灵们将时区作为他们计划过程的一部分。最早的时区目前Pacific/Kiribati是 UTC 前 14 小时。精灵们安排圣诞老人第一次到达那里。精灵们制定了一个飞行计划，将驯鹿带到午夜之后不久的其他时区，例如Pacific/Auckland。随着每个区域的午夜到来，他们继续向西前进。几小时后Asia/Kolkata，再过几小时Europe/Paris，再过几小时America/Montreal，依此类推。
Each of these specific delivery moments would be recorded by the elves using WITH TIME ZONE, while that general idea of Christmas would by stored as WITHOUT TIME ZONE.
这些特定的交付时刻中的每一个都将由精灵使用记录WITH TIME ZONE，而圣诞节的一般概念将存储为WITHOUT TIME ZONE.
Another use in business apps for WITHOUT TIME ZONEis scheduling appointments farther out than several weeks. Politicians around the world have an inexplicable predilection for messing with the clock and redefining time zone rules. They join Daylight Saving Time (DST), leave DST, start DST on a different date, or end DST on a different date, or shift their clocks by 15 minutes or half-hour. All of these have been done in last several years by Turkey, United States, Russia, Venezuela, and others.
商业应用程序的另一个用途WITHOUT TIME ZONE是安排比几周更远的约会。世界各地的政客都莫名其妙地喜欢弄乱时钟和重新定义时区规则。他们加入夏令时 (DST)，离开 DST，在不同的日期开始 DST，或在不同的日期结束 DST，或者将时钟移动 15 分钟或半小时。所有这些都是土耳其、美国、俄罗斯、委内瑞拉和其他国家在过去几年中完成的。
The politicians often make these changes with little forewarning. So if you are scheduling a dental appointment for six months out at 13:00, that should probably be stored as TIMESTAMP WITHOUT TIME ZONEor otherwise the politicians may effectively be changing you appointment to noon, or 2 PM, or 13:30.
政客们经常在没有事先警告的情况下做出这些改变。因此，如果您计划在 13:00 进行为期六个月的牙科预约，则可能应该将其存储，TIMESTAMP WITHOUT TIME ZONE否则政客可能会有效地将您的预约更改为中午、下午 2 点或 13:30。
回答by aspcartman
Update for the answers above: partitioning is no longer an exceptional case in PG11 thanks to pruning.
更新上述答案：由于修剪，分区不再是 PG11 中的特例。
https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITION-PRUNING
https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITION-PRUNING
Personally successfully tested queries against PG11 AWS RDS. Also the official PG wiki states the use of timestamp without timezoneis a bad idea:
亲自成功测试了针对 PG11 AWS RDS 的查询。此外，官方 PG wiki 声明使用timestamp without timezone是一个坏主意：
