更新时间:2023-02-15 15:40:52
我们如何存储 postgres datetime对象?
How do we store postgres datetime objects in java pojo classes for json objects?
它取决于。
TIMESTAMP WITH TIME ZONE
,请使用 java.time.Instant
class。 TIMESTAMP WITHOUT TIME ZONE
的Postgres列,请使用 java.time.LocalDateTime
class。TIMESTAMP WITH TIME ZONE
, use the java.time.Instant
class.TIMESTAMP WITHOUT TIME ZONE
, use the java.time.LocalDateTime
class.对于JSON,日期时间值没有JSON数据类型。生成标准ISO 8601格式的字符串。
As for JSON, there are no JSON data types for date-time values. Generate strings in standard ISO 8601 format.
我正在尝试对它们进行排序
I am trying to sort them
java.time 类知道如何自我排序,实现 可比较
界面。
The java.time classes know how to sort themselves, implementing the Comparable
interface.
如果我要比较日期时间或字符串?
if I should be comparing datetime or strings?
始终使用智能对象,而不是哑字符串。这就是为什么你有 JDBC 技术和 JDBC驱动程序。
Always use smart objects, not dumb strings. That is why you have JDBC technology and JDBC drivers.
日期compareTo不起作用
Date compareTo doesn't work
切勿使用 java.util.Date
类。切勿使用 java.sql.Date
类。仅使用 java.time 类。
Never use the java.util.Date
class. Never use the java.sql.Date
class. Use only java.time classes.
字符串comparTo适用于日期时间对象
strings comparTo works fine for datetime objects
不。日期时间字符串可以采用各种格式,使用各种人类语言和文化规范,具有各种时区或来自UTC的偏移量。字符串不适合于排序日期时间值。使用智能 java.time 对象,而不是哑字符串。
Nope. Date-time strings can come in all kinds of formats, using all kinds of human languages and cultural norms, with various time zones or offsets-from-UTC applied. Strings are not appropriate for sorting date-time values. Use smart java.time objects, not dumb strings.
或者在数据库方面进行排序,Postgres针对此类家务进行了优化。
Or do your sorting on the database side, where Postgres is optimized for such chores.
私人日期字段A;私人日期字段B;
private Date fieldA; private Date fieldB;
设为:
private Instant fieldA, fieldB ;
…
boolean isAEarlier = fieldA.isBefore( fieldB ) ;
boolean isAtheSame = fieldA.equals( fieldB ) ; // Note that some other java.time classes have `isEqual` method as well as `equals` method.
boolean isALater = fieldA.isAfter( fieldB ) ;
boolean isAEqualToOrLaterThan = ( ! fieldA.isBefore( fieldB ) ) ; // "Is equal to or later than" is a common use-case. "Not before" is a logical shortcut with the same effect.
日期
类现在是遗留的,是几年前由 java.time 类取代的非常麻烦的旧日期时间类的一部分。切勿使用日期
,日历
, SimpleDateFormat
等。
The Date
class is now legacy, part of the terribly troublesome old date-time classes that were supplanted by the java.time classes years ago. Never use Date
, Calendar
, SimpleDateFormat
, and such.
您的问题与其他许多问题重复,因此我将在此简要介绍。搜索Stack Overflow以了解更多信息。
Your Question is a duplicate of many others, so I'll be brief here. Search Stack Overflow to learn more.
对于数据库列类型 TIMESTAMP WITH TIME ZONE
在SQL标准中定义并在Postgres中使用,表示时刻,即时间轴上的特定点。
For the database column type TIMESTAMP WITH TIME ZONE
defined in the SQL standard and used in Postgres, that represents a moment, a specific point on the timeline.
在 Postgres 中,此类型分辨率为微秒, 总是在 UTC 。具有时区指示符或从UTC偏移量的任何输入都将调整为UTC,然后丢弃区域/偏移量。所以类型有点用词不当,因为忘记了原始区域/偏移,并且存储的值始终是UTC。其他数据库可能会因此行为而异,因此请注意,因为SQL规范几乎没有涉及日期时间的主题。
In Postgres, this type has a resolution of microseconds and is always in UTC. Any inputs with an indicator of time zone or offset-from-UTC are adjusted into UTC, and the zone/offset then discarded. So the type is a bit of a misnomer, as the original zone/offset is forgotten and the stored value is always in UTC. Other databases may vary in this behavior, so beware, as the SQL spec barely touches on the subject of date-time.
请注意,使用 JDBC ,您的工具可能正在注入时区或从UTC的偏移量;这对新手来说可能是一种误导和混淆(在我看来这是一个不幸的设计决定)。
Beware that when using tools other than JDBC, your tool may be injecting a time zone or offset-from-UTC after retrieving the stored UTC value; this can be quite misleading and confusing to a novice (and is an unfortunate design decision in my opinion).
在Java中,通常***在 UTC 。作为程序员,学习思考,存储和交换时刻为UTC。通常,为此使用 Instant
类。要在类中定义成员变量, Instant
是您的首选课程。
In Java, generally best to work in UTC. As a programmer, learn to think, store, and exchange moments as UTC. Generally, use the Instant
class for this. For defining member variables in your classes, Instant
is your go-to class.
Instant instant = Instant.now() ; // Capture the current moment in UTC, with a resolution as fine as nanoseconds.
您可能希望截断任何纳秒到微秒以匹配Postgres的检索值。使用 ChronoUnit指定分辨率
。
You may want to truncate any nanoseconds to microseconds to match retrieved values from Postgres. Specify resolution with ChronoUnit
.
Instant instant = Instant.now().truncatedTo( ChronoUnit.MICROS ) ;
要在期望/预期时区内向用户显示,请指定 ZonedId
获取 ZonedDateTime
。
For presentation to the user in their desired/expected time zone, assign a ZonedId
to get a ZonedDateTime
.
指定正确的时区名称,格式为 continent / region
,例如 America / Montreal
, 非洲/卡萨布兰卡
,或太平洋/奥克兰
。切勿使用3-4字母缩写,例如 EST
或 IST
,因为它们不真正的时区,非标准化,甚至不是唯一的(!)。
Specify a proper time zone name in the format of continent/region
, such as America/Montreal
, Africa/Casablanca
, or Pacific/Auckland
. Never use the 3-4 letter abbreviation such as EST
or IST
as they are not true time zones, not standardized, and not even unique(!).
ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;
要返回UTC,请提取即时
。
To get back to UTC, extract a Instant
.
Instant instant = zdt.toInstant() ;
生成表示 ZonedDateTime $ c $值的本地化文本c> object,使用
DateTimeFormatter
。搜索Stack Overflow以获取更多信息。
To generate localized text representing the value of the ZonedDateTime
object, use DateTimeFormatter
. Search Stack Overflow for much more info.
数据库类型 TIMESTAMP没有时区
故意缺乏任何时区概念或从UTC偏移。因此,它不代表片刻,不时间轴上的一个点,并且不您在商业应用中可能想要的内容除外when:
The database type TIMESTAMP WITHOUT TIME ZONE
purposely lacks any concept of time zone or offset-from-UTC. As such it does not represent a moment, is not a point on the timeline, and is not what you likely want in a business app except when:
In Postgres,忽略伴随输入的任何区域或偏移。日期和时间按原样存储,无需调整。
In Postgres, any zone or offset accompanying input is ignored. The date and the time-of-day are stored as-is with no adjustment.
Java中的匹配类型是 LocalDateTime
。
The matching type in Java is LocalDateTime
.
此类名称中的本地不表示特定地区。恰好相反!它表示每个位置,或任何位置,但不是特定的位置。如果你不理解这一点,做一些研究,阅读类文档,并搜索Stack Overflow。
The "Local" in this class name does not mean "a particular locality". Just the opposite! It means every locality, or any locality, but not a particular locality. If you do not understand this, do some study, read the class doc, and search Stack Overflow.
使用智能对象而不是哑字符串与数据库交换日期时间值。
Use smart objects rather than dumb strings to exchange date-time values with your database.
从 JDBC开始4.2 ,您可以直接与数据库交换 java.time 对象。永远不要使用 java.sql.Timestamp
, java.sql.Date
和 java.sql .Time
。
As of JDBC 4.2, you can directly exchange java.time objects with the database. Never use java.sql.Timestamp
, java.sql.Date
, and java.sql.Time
.
存储。
myPreparedStatement.setObject( … , instant ) ;
检索。
Instant instant = myResultSet.getObject( … , Instant.class ) ;
JSON 规范定义的数据类型非常少,而且没有一个与日期时间相关。你在那里自己。同样适用于 XML 。
The JSON spec defines very few data types, and none of them are date-time related. You are on your own there. Ditto for XML.
将日期时间值序列化为文本时,请使用标准 ISO 8601 格式。这些旨在实用和有用,并避免含糊不清。它们的设计易于通过机器进行解析,同时也易于被不同文化的人阅读。
When serializing date-time values as text, use the standard ISO 8601 formats. These are designed to be practical and useful, and to avoid ambiguities. They are designed to be easy to parse by machine, while also being easy to read by humans across cultures.
java.time 类使用解析/生成日期时间字符串时默认使用这些标准格式。只需在各种类上调用解析
和 toString
。
The java.time classes use these standard formats by default when parsing/generating date-time strings. Just call parse
and toString
on the various classes.
Instant instant = Instant.parse( "2018-01-23T01:23:45.123456Z" ) ;
String output = instant.toString() ;
一时间的ISO 8601格式碰巧与通常的SQL格式相似,只是在SQL中使用中间的空格而不是 T
。这个事实在很大程度上是无关紧要的,因为你应该在Java和数据库之间使用对象而不是字符串,如上所述。
The ISO 8601 format for a moment happen to be similar to the usual SQL format except that in SQL uses a SPACE in the middle rather than a T
. That fact is largely irrelevant as you should be using objects rather than strings between Java and your database, as mentioned above.
与比较主题相关...在处理时间跨度时,学会始终使用半开放式方法,其中开头是包含,而结尾是独家。搜索Stack Overflow了解更多信息。
Related to the topic of comparing… When working with spans of time, learn to consistently use the Half-Open approach where the beginning is inclusive while the ending is exclusive. Search Stack Overflow to learn more.
java.time 框架内置于Java 8及更高版本中。这些类取代了麻烦的旧遗留日期时间类,例如 java.util.Date
, 日历
,& SimpleDateFormat
。
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date
, Calendar
, & SimpleDateFormat
.
Joda-Time 项目,现已进入维护模式 ,建议迁移到 java.time classes。
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
要了解更多信息,请参阅 Oracle Tutorial 。并搜索Stack Overflow以获取许多示例和解释。规范是 JSR 310 。
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
您可以直接与数据库交换 java.time 对象。使用符合 JDBC驱动程序 / jeps / 170rel =nofollow noreferrer> JDBC 4.2 或更高版本。不需要字符串,不需要 java.sql。*
类。
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.*
classes.
从哪里获取java.time班?
Where to obtain the java.time classes?
ThreeTen-Extra 项目使用其他类扩展java.time。该项目是未来可能添加到java.time的试验场。您可以在这里找到一些有用的课程,例如 Interval
, YearWeek
, YearQuarter
,更多。