Convert from java.sql.Timestamp to ISO_OFFSET_DATE_TIME String then to OffsetDateTime?

Question

I have a "Don't ask why just do it this way" question I hope someone can help me with.

I am working on updating a Java app and I cannot change certain classes. The problem is this:

I read a java.sql.Timestamp from an Oracle DB.
The Timestamp needs to be converted to the standard ISO_OFFSET_DATE_TIME as a String.
This String then needs to be parsed into a java.time.OffsetDateTime value.

I cannot get the Timestamp to be readable by any of the java.time classes.

Is there a way around this? How does a java.sql.Timestamp get imported into a java.time.OffsetDateTime?

The connection with performance escapes me. Don't tag indiscriminately. — user207421
– user207421, Commented May 4, 2022 at 3:39

Basil Bourque · Accepted Answer · 2022-05-04 00:34:07Z

1

tl;dr

myTimestamp
.toInstant()
.atOffset( 
    ZoneOffset.UTC 
)

Details

I read a java.sql.Timestamp from an Oracle DB.

When handed an object from the terribly flawed legacy date-time classes, immediately convert to the modern java.time replacement class. That class would be Instant, for representing a moment as seen with an offset fromUTC of zero hours-minutes-seconds.

Conversion methods

To convert, call the new to…/from…/valueOf conversion methods added to the old classes.

Instant instant = myTimestamp.toInstant() ;

The Timestamp needs to be converted to the standard ISO_OFFSET_DATE_TIME as a String.

“converted” is the wrong word to use here. ISO_OFFSET_DATE_TIME refers to a constant holding a predefined formatter. A formatter up is used to parse text to get a date-time object, and is used to generate text representing the content of a date-time object. But a date-time object itself has no “format” as it does not consist of text.

Indeed, you have no need for text at all here in your scenario. As mentioned above, the old legacy class have been gifted with conversion methods.

This String then needs to be parsed into a java.time.OffsetDateTime value.

Apply an offset (ZoneOffset) to your Instant to get a OffsetDateTime.

I assume you want to stick with an offset of zero. So use the constant ZoneOffset.UTC.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Avoid `Timestamp`

You can skip this conversion chore by extracting an OffsetDateTime object from your database. No need to ever use Timestamp again in JDBC 4.2+.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

And writing an OffsetDateTime.

myPreparedStatement.setObject( … , odt ) ;

edited May 4, 2022 at 0:34

answered May 4, 2022 at 0:25

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1071914 Over a year ago

Thank you. I agree that this is a bewildering sequence, the problem is that I'm taking the value from an Oracle database, into a JSON object, to a Java Map, then to a different POJO, and I can't change three of those four destinations. Hence the bizarro-world translations. Again, thank you.

rzwitserloot · Accepted Answer · 2022-05-04 00:26:55Z

I have a "Don't ask why just do it this way" question I hope someone can help me with.

Okay. But what you want is farcically crazy.

Timestamp is specifically equivalent to Instant, in that they both represent 'milliseconds since the epoch'.

This in sharp contrast to an OffsetDateTime which is different; that represents a combination of year, month, day, hour, minute, second, millis in the second, and a timezone offset.

Thus, the strategy to convert is not to 'silently' swap from one fundamental concept to another, only convert from different fundamentals with explicit calls. Thus, we first convert the Timestamp to Instant as that's still the same fundamental concept, and then we use the nice java.time API to explicitly swap on over to OffsetDateTime.

NB: Timestamp extends java.util.Date. Be aware that this class is a complete and utter lie - j.u.Date instances do not represent dates. This can, naturally, be confusing! Date is of the same fundamental concept as Instant, just the same: It's millis-since-epoch. Hence why all the methods in Date (such as .getYear()) are deprecated. This is asking a question to a fundamental that cannot properly answer it. Instant doesn't have a getYear either, because you can't answer that question with zone info which instants do not have.

on to the code!

// This should probably be a 'private static final' constant:
ZoneOffset DESIRED_OFFSET = ZoneOffset.ofHours(5);

// the actual code:

Instant i = yourSqlTimestamp.toInstant();
OffsetDateTime odt = i.atOffset(DESIRED_OFFSET);
String result = odt.format(FORMAT_PATTERN);
OffsetDateTime thisIsPointless = OffsetDateTime.parse(result, FORMAT_PATTERN);

As you might imagine, those last 2 lines are pointless.

Collectives™ on Stack Overflow

Convert from java.sql.Timestamp to ISO_OFFSET_DATE_TIME String then to OffsetDateTime?

2 Answers 2

tl;dr

Details

Conversion methods

Avoid `Timestamp`

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

tl;dr

Details

Conversion methods

Avoid Timestamp

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Avoid `Timestamp`