Java 将 OffsetDateTime 转换为 UTC 时间戳

Convert OffsetDateTime to UTC Timestamp

提问by Cheetah

I have an java.time.OffsetDateTimewhich I would like to convert to a java.sql.Timestamp. Since Timestampdoesn't store any offset information, I am going to store all dates/times in the database as UTC.

我有一个java.time.OffsetDateTime我想转换为java.sql.Timestamp. 由于Timestamp不存储任何偏移信息，我将在数据库中将所有日期/时间存储为 UTC。

How do I convert the OffsetDateTimeto a Timestampwhich is in UTC?

如何转换OffsetDateTime到Timestamp这是在UTC？

EDIT:

编辑：

I believe this is the answer but it seems are rather convoluted way to covert to UTC:

我相信这是答案，但似乎是转换为 UTC 的相当复杂的方式：

OffsetDateTime dateTime = OffsetDateTime.now();
Timestamp timestamp = Timestamp.valueOf(dateTime.atZoneSameInstant(ZoneId.of("Z")).toLocalDateTime());

采纳答案by Notso

This would be a way to do the conversion and ensure UTC is used. That I think is a little cleaner than solution proposed using the epoch seconds.

这将是一种进行转换并确保使用 UTC 的方法。我认为这比使用纪元秒提出的解决方案更清晰。

Timestamp test = Timestamp.valueOf(entityValue.atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime());

回答by Glorfindel

Use .toEpochSecond()to get the # of seconds from the reference date (which is in UTC), multiply by 1000 and pass this to the Timestampconstructor (as it expects milliseconds).

用于.toEpochSecond()从参考日期（以 UTCTimestamp为单位）获取秒数，乘以 1000 并将其传递给构造函数（因为它期望毫秒）。

new Timestamp(1000 * offsetDateTime.toEpochSecond());

回答by rve

Another solution would be:

另一种解决方案是：

Timestamp.valueOf(LocalDateTime.ofInstant(dateTime.toInstant(), ZoneOffset.UTC));

It converts the dateTimeto UTC, strips the timezone information and then converts the result to a Timestamp. It is still convoluted but IMHO it's a bit cleaner.

它将 转换dateTime为 UTC，去除时区信息，然后将结果转换为Timestamp. 它仍然令人费解，但恕我直言，它更干净一些。

Just using toInstance()or toEpochSeconds()will adjust the result with the offset provided.

只需使用toInstance()或toEpochSeconds()将使用提供的偏移量调整结果。

The following shows the test results from this and the other answers:

下面显示了这个答案和其他答案的测试结果：

OffsetDateTime dateTime = 
    OffsetDateTime.of(2015, 10, 23, 12, 44, 43, 0, ZoneOffset.UTC);
    // OffsetDateTime.of(2015, 10, 23, 12, 44, 43, 0, ZoneOffset.ofHours(-5));

err.println("dateTime            = " 
    + dateTime
);

err.println("as LocalDateTime    = " 
    + dateTime.toLocalDateTime()
);

err.println("as timestamp (mine) = " 
    + Timestamp.valueOf(LocalDateTime.ofInstant(dateTime.toInstant(), ZoneOffset.UTC))
);

err.println("@Cheetah (correct)  = " 
    + Timestamp.valueOf(dateTime.atZoneSameInstant(ZoneId.of("Z"))
        .toLocalDateTime())
);

err.println("@Notso (wrong)      = " 
    + Timestamp.from(dateTime.toInstant())
);

err.println("@Glorfindel (wrong) = " 
    + new Timestamp(1000 * dateTime.toEpochSecond())
);

which gives the following results (my timezone is CET) :

这给出了以下结果（我的时区是 CET）：

(with ZoneOffset.UTC)
dateTime            = 2015-10-23T12:44:43Z
as LocalDateTime    = 2015-10-23T12:44:43
as timestamp (mine) = 2015-10-23 12:44:43.0
@Cheetah (correct)  = 2015-10-23 12:44:43.0
@Notso (wrong)      = 2015-10-23 14:44:43.0
@Glorfindel (wrong) = 2015-10-23 14:44:43.0

(with ZoneOffset.ofHours(-5))
dateTime            = 2015-10-23T12:44:43-05:00
as LocalDateTime    = 2015-10-23T12:44:43
as timestamp (mine) = 2015-10-23 17:44:43.0
@Cheetah (correct)  = 2015-10-23 17:44:43.0
@Notso (wrong)      = 2015-10-23 19:44:43.0
@Glorfindel (wrong) = 2015-10-23 19:44:43.0

(The version from Notso above was before his edit of Feb 17 2016)

（上面来自 Notso 的版本是他在 2016 年 2 月 17 日编辑之前的版本）

回答by Ole V.V.

I am providing the modern answer.

我正在提供现代答案。

java.time and JDBC 4.2

java.time 和 JDBC 4.2

You should avoid the Timestampclass. It's poorly designed and very confusing, a true hack on top of the already poorly designed java.util.Dateclass. The fact that the other answers lead to different results as documented by the comparisons in the answer by rve in my opinion illustrates the confusion very well. You are already using OffsetDateTimefrom java.time, the modern Java date and time API, and provided that you have got a JDBC 4.2 compliant JDBC driver, you can and should stick to the classes from java.time.

你应该避免Timestamp上课。它的设计很差，而且非常混乱，是在已经设计得很差的java.util.Date课程之上的真正黑客。在我看来，其他答案会导致不同的结果，正如 rve 在答案中的比较所记录的那样，这一事实很好地说明了这种混淆。您已经在使用OffsetDateTimejava.time 中的现代 Java 日期和时间 API，并且如果您有一个 JDBC 4.2 兼容的 JDBC 驱动程序，您可以并且应该坚持使用 java.time 中的类。

Best to store as timestamp with time zone

最好存储为 timestamp with time zone

Storing dates and times in UTC in the database as you say you want is a good and recommended practice. If you can, change the datatype in the database to timestamp with time zone. While this doesn't store a time zone (despite the name), it makes sure that the database too “knows” that timestamps are in UTC, which already prevents many mistakes. Next advantage is that (provided that I have understood correctly) you can store your OffsetDateTimedirectly and let the conversion to UTC happen automatically.

按照您的意愿在数据库中以 UTC 格式存储日期和时间是一种很好的推荐做法。如果可以，请将数据库中的数据类型更改为timestamp with time zone. 虽然这不存储时区（不管名称如何），但它确保数据库也“知道”时间戳是 UTC，这已经防止了许多错误。下一个优点是（前提是我理解正确）您可以OffsetDateTime直接存储您的数据并让转换为 UTC 自动发生。

    OffsetDateTime odt = OffsetDateTime.of(
            2015, 6, 4, 19, 15, 43, 210987000, ZoneOffset.ofHours(1));
    PreparedStatement stmt = yourDbConnection.prepareStatement(
            "insert into your_table (your_timestamp_with_time_zone) values (?);");
    stmt.setObject(1, odt);
    stmt.executeUpdate();

If you want to make it clearer in your Java code that the time is stored in UTC, convert explicitly first:

如果您想在 Java 代码中更清楚地说明时间是以 UTC 存储的，请先进行显式转换：

    odt = odt.withOffsetSameInstant(ZoneOffset.UTC);

If your database stores timestampwithout time zone

如果您的数据库存储timestamp没有时区

If the datatype in your database is a mere timestamp(without time zone) (not recommended), the type to use on the Java side is LocalDateTime. I'd do the conversion to UTC like this:

如果数据库中的数据类型仅仅是timestamp（没有时区）（不推荐），那么在 Java 端使用的类型是LocalDateTime. 我会像这样转换为UTC：

    LocalDateTime ldt = odt.withOffsetSameInstant(ZoneOffset.UTC).toLocalDateTime();
    System.out.println("UTC datetime        = " + ldt);

Output is:

输出是：

UTC datetime = 2015-06-04T18:15:43.210987

UTC 日期时间 = 2015-06-04T18:15:43.210987

Storing into the database is similar to before:

存入数据库与之前类似：

    PreparedStatement stmt = yourDbConnection.prepareStatement(
            "insert into your_table (your_timestamp) values (?);");
    stmt.setObject(1, ldt);