PostgreSQL/JDBC和TIMESTAMP与TIMESTAMPTZ [英] PostgreSQL/JDBC and TIMESTAMP vs. TIMESTAMPTZ

问题描述

最近，使用JPA处理Timestamps时，我经历了很多痛苦.我发现，通过对字段使用TIMESTAMPTZ而不是TIMESTAMP可以解决许多问题.我的服务器在UTC中，而JVM在PST中.使用TIMESTAMP WITHTIME TIMEZONE时，使用JPA几乎不可能对数据库中的UTC值进行规范化.

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.

对于我来说，我将这些字段用于诸如用户何时创建"，他们最后一次使用设备的时间"，何时他们最后一次收到警报的时间"之类的内容.这些通常是事件，因此它们是时间排序值中的实例.而且由于它们现在将在TIMESTAMPTZ启用，因此，如果我不希望它们采用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.

所以我的问题是，对于Java/JPA/PostgreSQL服务器，何时要在TIMESTAMPTZ上使用TIMESTAMP?有哪些用例?现在，我很难理解为什么要使用TIMESTAMP，因此，我担心自己无法掌握它的价值.

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.

推荐答案

您可以使用它来表示Joda-Time和新的Java 8 time API称为LocalDateTime的东西. LocalDateTime并不表示时间轴上的精确点.它只是一组字段，从一年到十亿分之一秒.它是用于生日的日期的描述，以及在挂钟上看到的本地时间".

You could use it to represent what Joda-Time and the new Java 8 time APIs call a LocalDateTime. A LocalDateTime doesn'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".

例如，您可以使用它来表示您的确切生日是1975-07-19，下午6点.或那样，在全球范围内，下一个新年将在2015年1月1日的00:00进行庆祝.

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.

为了表示精确的时刻，例如阿姆斯特朗在月球上行走的时刻，带有时区的时间戳确实更合适.不管JVM的时区和数据库的时区如何，它都应该返回正确的时刻.

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.

这篇关于PostgreSQL/JDBC和TIMESTAMP与TIMESTAMPTZ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！