将java.util.Date转换为PostgreSQL的Time Zone时区

I am trying to insert a date into my PostgreSQL db. Here is how I parse the date in my java application:

我想在我的PostgreSQL数据库中插入一个日期。以下是我在java应用程序中解析日期的方法:

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
Date parsedTimeStamp = dateFormat.parse(dateString);

And in my PostgreSQL db. I have it as Timestamp with Time Zone. What is the ideal way to process and insert it?

在我的PostgreSQL数据库中。我把它作为时区的时间戳。处理和插入它的理想方法是什么?

Example of what the db expects 2017-09-09 07:15:33.451061+00.

db预期2017-09-09 07:15:33.451061 + 00的例子。

3 个解决方案

#1

SimpleDateFormat you use here translates a string, to a date, in your example, this must mean that the format you use id "yyyyMMdd" - for example 20171217 (today).

您在这里使用的SimpleDateFormat将字符串转换为日期,在您的示例中,这必须表示您使用的格式为“yyyyMMdd” - 例如20171217(今天)。

To create the string for the DB you need to take the opposite approach. From a date object to a string.

要为DB创建字符串,您需要采取相反的方法。从日期对象到字符串。

You can create your own formatter for that (using your example)

您可以为此创建自己的格式化程序(使用您的示例)

    Date date = new Date();
    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss.SSSSSSX");
    String text = formatter.format(date);

You can read more about formatting here

您可以在此处详细了解格式

#2

tl;dr

myPreparedStatement.setObject( 
    … , 
    LocalDate.parse( "20170909" , DateTimeFormatter.BASIC_ISO_DATE )  // Parse string into a date-only object, a `LocalDate` object.
             .atStartOfDay( ZoneId.of( "Asia/Kolkata" ) )             // Apply a time zone to determine the first moment of the day on that date, producing a `ZonedDateTime` object.
             .toInstant()                                             // Extract an `Instant` object, a moment always in UTC. This last step is not technically required, as your JDBC driver with Postgres should effectively do this on your behalf. But this line completes the demo conceptually.
) ;

• Work in objects, not strings, between Java & database.

在Java和数据库之间处理对象,而不是字符串。

• Use only java.time classes, not legacy date-time classes.

仅使用java.time类,而不是旧版日期时间类。

• Let your JDBC driver + Postgres do the heavy-lifting.

让你的JDBC驱动程序+ Postgres做繁重的工作。

• Specify time zone.

指定时区。

• Always use a PreparedStatement to avoid SQL injection security risks.

始终使用PreparedStatement来避免SQL注入安全风险。

Avoid legacy classes

The other Answers are outdated, using troublesome old date-time classes that are now legacy, supplanted by the java.time classes.

其他答案已经过时,使用了现在遗留下来的麻烦的旧日期时间类,取而代之的是java.time类。

java.time

With a JDBC driver supporting JDBC 4.2 and later, you can exchange java.time objects with the Postgres server. No need for the java.sql date-time classes, nor the terrible Date and Calendar classes.

使用支持JDBC 4.2及更高版本的JDBC驱动程序,您可以使用Postgres服务器交换java.time对象。不需要java.sql日期时间类,也不需要可怕的Date和Calendar类。

Persisting:

Instant instant = Instant.now() ;
myPreparedStatement.setObject( … , instant ) ;

…and retrieving:

Instant instant = myResultSet( … , Instant.class ) ;

In standard SQL, a TIMESTAMP WITH TIME ZONE is a moment, a specific point on the timeline. In other words, a date, a time-of-day, and a time zone. In contrast, you have only a date, lacking the time-of-day and the time zone. You likely want to use the first moment of the day on that particular date in a particular time zone.

在标准SQL中,TIMESTAMP WITH TIME ZONE是时刻,是时间轴上的特定点。换句话说,日期,时间和时区。相比之下,您只有一个日期,缺少时间和时区。您可能希望在特定时区的特定日期使用当天的第一时刻。

To change your date-only value to a moment, first make a LocalDate object.

要将仅限日期的值更改为片刻,请首先创建一个LocalDate对象。

LocalDate ld = LocalDate.parse( "20170909" , DateTimeFormatter.BASIC_ISO_DATE ) ; 

Next, call that LocalDate object’s atStartOfDay method while passing a ZoneId.

接下来,在传递ZoneId时调用LocalDate对象的atStartOfDay方法。

A time zone is required to lend meaning to a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

需要一个时区来为日期赋予意义。对于任何给定的时刻,日期在全球范围内因地区而异。例如,法国巴黎午夜过后几分钟,在魁北克蒙特利尔的“昨天”仍然是新的一天。

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(!).

以洲/地区的格式指定适当的时区名称,例如America / Montreal,Africa / Casablanca或Pacific / Auckland。切勿使用3-4字母缩写,例如EST或IST,因为它们不是真正的时区,不是标准化的,甚至不是唯一的(!)。

ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = ld.atStartOfDay( z ) ;

Finally extract a Instant.

最后提取一个Instant。

Instant instant = zdt.toInstant() ; 

At this point you can insert that Instant into database as discussed above.

此时,您可以将Instant插入数据库,如上所述。

Objects, not strings

Your work will be easier if you learn to think in terms of objects rather than mere strings when exchanging data with a database from Java.

如果您在与Java数据库交换数据时学会用对象而不仅仅是字符串进行思考,那么您的工作会更容易。

TIMESTAMP WITH TIME ZONE

By the way, be clear on how Postgres uses the data type TIMESTAMP WITH TIME ZONE. The SQL standard defines this data type all too briefly, and databases vary in their implementation. The following describes behavior of Postgres versions 8, 9, and 10.

顺便说一句,要明确Postgres如何使用数据类型TIMESTAMP WITH TIME ZONE。 SQL标准过于简单地定义了这种数据类型,并且数据库的实现也各不相同。以下描述了Postgres版本8,9和10的行为。

Any date-time value submitted with a time zone or offset-from-UTC is processed by using that zone/offset to determine a value in UTC. That UTC value is then stored in the database, with a resolution of microseconds (not the milliseconds of legacy Java java.util.Date & Calendar classes, and not the nanoseconds of java.time classes). After the UTC adjustment is made, the zone/offset information is discarded. If you care about the original zone/offset, store that in a separate column explicitly.

使用该区域/偏移量来处理使用时区或从UTC偏移量提交的任何日期时间值,以确定UTC中的值。然后将该UTC值存储在数据库中,其分辨率为微秒(不是传统Java java.util.Date和Calendar类的毫秒数,而不是java.time类的纳秒数)。在进行UTC调整之后,丢弃区域/偏移信息。如果您关心原始区域/偏移,请将其明确存储在单独的列中。

When retrieved, the date-time value is sent out from Postgres in UTC. An intervening tool such as psql or pgAdmin may confusingly apply a default time zone. While well-intentioned, such a feature creates the illusion of the stored value carrying a time zone when in fact it does not. In contrast, a JDBC driver compliant with JDBC 4.2 and later will handle the UTC and zone automatically for you. Generally I suggest always retrieving a Instant object as shown in code above. Then apply zones if required to instantiate your own ZonedDateTime or OffsetDateTime objects as shown in code above.

检索时,日期时间值将以UTC格式从Postgres发出。诸如psql或pgAdmin之类的干预工具可能会混淆地应用默认时区。善意的,这样的特征创造了携带时区的储值的错觉,而事实上并非如此。相反,符合JDBC 4.2及更高版本的JDBC驱动程序将自动为您处理UTC和区域。通常我建议总是检索一个Instant对象,如上面的代码所示。然后根据需要应用区域来实例化您自己的ZonedDateTime或OffsetDateTime对象,如上面的代码所示。

---

About java.time

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.

java.time框架内置于Java 8及更高版本中。这些类取代了麻烦的旧遗留日期时间类,如java.util.Date,Calendar和SimpleDateFormat。

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

现在处于维护模式的Joda-Time项目建议迁移到java.time类。

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

要了解更多信息,请参阅Oracle教程。并搜索Stack Overflow以获取许多示例和解释。规范是JSR 310。

Where to obtain the java.time classes?

从哪里获取java.time类?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.

  带有捆绑实现的标准Java API的一部分。

  • Java 9 adds some minor features and fixes.

  Java 9增加了一些小功能和修复。

Java SE 8,Java SE 9和更高版本内置。带有捆绑实现的标准Java API的一部分。 Java 9增加了一些小功能和修复。

• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.

  许多java.time功能都被反向移植到ThreeTen-Backport中的Java 6和7。

Java SE 6和Java SE 7许多java.time功能都被反向移植到ThreeTen-Backport中的Java 6和7。

• Android
  • The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.

  ThreeTenABP项目特别适用于Android的ThreeTen-Backport(如上所述)。

  • See How to use ThreeTenABP….

  请参见如何使用ThreeTenABP ....

Android ThreeTenABP项目专门针对Android调整ThreeTen-Backport(如上所述)。请参见如何使用ThreeTenABP ....

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

ThreeTen-Extra项目使用其他类扩展了java.time。该项目是未来可能添加到java.time的试验场。您可以在这里找到一些有用的课程,如Interval,YearWeek,YearQuarter等。

#3

From what I saw in the Postgres documentation for JDBC mapping types, Postgres expects a java.sql.Timestamp to be used to map to a timestamp with time zone type. If you were working with a prepared statement, you could use this:

根据我在Postgres文档中看到的JDBC映射类型,Postgres期望使用java.sql.Timestamp映射到具有时区类型的时间戳。如果您正在使用预准备语句,则可以使用:

java.sql.Timestamp ts = new Timestamp(parsedTimeStamp.getTime());
PreparedStatement stmt;
// define the statement using Postgres SQL with placeholders
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
// now set the timestamp at the particular timezone in the statement
stmt.setTimestamp(index, ts, cal);

Replace GMT with whatever timezone you actually want, and replace index with the numerical index of the placeholder where the timestamp should appear in the actual SQL.

将GMT替换为您实际需要的任何时区,并将index替换为占位符的数字索引,其中时间戳应出现在实际SQL中。

Note that more recent drivers which are Java 8 compliant should support using LocalDateTime in addition to java.sql.Timestamp.

请注意,除了java.sql.Timestamp之外,更新的Java 8兼容驱动程序应该支持使用LocalDateTime。