How to format Oracle date and timestamp type values in Java?

Question

I have date and timestamp type fields in oracle db, I need to retrieve these values and map them to my object field. Though I format the values I do not get the expected result. Here is the my code snippet.

import java.util.Date;
public class Operation{
private Date created;
private Date valueDate;

public Date getValueDate() {
    return this.valueDate;
}
public void setValueDate(Date valueDate) {
this.valueDate = valueDate;
}
public Date getCreated() {
    return this.valueDate;
}
public void setCreated(Date created) {
this.created= created;
}
}


//here starts code snippet to call db method

SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSS");
SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String formatCreated = df1.format(result.getTimestamp(22)); //Input from db: 23-FEB-18 06.17.42.302680 PM 
//OutputFormat 2018-02-23 18:17:42.000302  
String formatValueDate = df2.format(result.getTimestamp(23));//Input from db:23.02.2018 18:17:42 
//OutputFormat 2018-02-23 18:17:42
Operation op = new Operartion();
op.setCreated(df1.parse(formatCreated)) //Output Fri Feb 23 18:17:42 GMT+04:00 2018
op.setCreated(df1.parse(formatValuedate)) //Output Fri Feb 23 18:17:42 GMT+04:00 2018

Any help appreciated!

If you are at least at Java 8, consider using the new Date/Time API instead. — Axel
– Axel, Commented Mar 2, 2018 at 14:51
So formatCreated doesn't show milliseconds, but parsing it returns them? — Lance Toth
– Lance Toth, Commented Mar 2, 2018 at 14:52
OK then. What do you really want to do? op.setCreated(result.getTimestamp(22))? — Axel
– Axel, Commented Mar 3, 2018 at 5:17
@Axel, My apologies, pls see my updates on inputs/outputs. I have commented them out — devgirl
– devgirl, Commented Mar 3, 2018 at 14:41

Basil Bourque · Accepted Answer · 2018-03-02 20:55:43Z

2

This has been covered many times already on Stack Overflow. Search before posting.

So briefly…

Use java.time

Use modern java.time classes, rather than the troublesome old legacy date-time classes such as Date/Calendar.

Use smart objects, not dumb strings

As of JDBC 4.2, exchange java.time objects directly with the database.

Use Instant for TIMESTAMP WITH TIME ZONE
Use LocalDateTime for TIMESTAMP WITHOUT TIME ZONE
Use LocalDate for DATE

Call PreparedStatement::setObject and ResultSet::getObject.

myPreparedStatement.setObject( … , instant ) ;

And…

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

Note that no strings were used at all.

//Input from db: 23-FEB-18 06.17.42.302680 PM

Incorrect. Your assumption is false. The database uses its own internally-defined binary format to store date-time values, not strings/text. Do not conflate date-time values with their textual representations. In other words, date-time values do not have a “format”.

Strings

Generate strings in standard ISO 8601 format by calling toString on the java.time objects.

String output = instant.toString() ;

For other formats, use DateTimeFormatter instead of SimpleDateFormat. Already covered well on Stack Overflow, so search.

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.

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

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

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.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, and later
- Built-in.
- Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
- Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use 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.

edited Mar 2, 2018 at 20:55

answered Mar 2, 2018 at 16:41

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

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

2 Comments

devgirl Over a year ago

Thanks for your answer. I am well informed about java.time API,but since jre v6 is used in the project, I do not have this option. So any suggestion that would work with jre v6?

Basil Bourque Over a year ago

@devgirl For Java 6, use the ThreeTen-Backport library. See the last bullets in my Answer. The back-port’s API mirrors java.time as much as possible. Later, when you eventually update to Java 8 or later, you’ll need do little more than change your import statements to phase out the back-port. Well worth the bother of adding a library dependency to your project; the legacy date-time classes are a wretched mess and should be avoided.

Collectives™ on Stack Overflow

How to format Oracle date and timestamp type values in Java?

1 Answer 1

Use java.time

Use smart objects, not dumb strings

Strings

About java.time

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Use java.time

Use smart objects, not dumb strings

Strings

About java.time

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related