Convert OffsetDateTime to UTC Timestamp

Question

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

How do I convert the OffsetDateTime to a Timestamp which is in UTC?

EDIT:

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

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

Answer 1

I am providing the modern answer.

java.time and JDBC 4.2

You should avoid the Timestamp class. It’s poorly designed and very confusing, a true hack on top of the already poorly designed java.util.Date class. 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 OffsetDateTime from 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.

Best to store as 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 OffsetDateTime directly and let the conversion to UTC happen automatically.

    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:

    odt = odt.withOffsetSameInstant(ZoneOffset.UTC);

If your database stores timestamp without time zone

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:

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

Output is:

UTC datetime = 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);

Answer 2

Another solution would be:

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

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

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

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) :

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

Answer 3

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.

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

Answer 4

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

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