1

I'm trying to retrieve a field from my database table.

column name - create_timestamp

Data type of my DB2 column - Timestamp

Format in DB2 - yyyy-MM-dd HH:mm:ss.SSSSSS

I'm able to view data in this format in DB visualizer

I'm reading this value from resultset in java using

bean.setCreateTimestamp(rs.getTimestamp(create_timestamp));

createTimestamp field of bean is of java.sql.Timestamp type

When I print using bean.getCreateTimestamp(), the last three numbers of Actual DB2 value: 2016-01-30 11:43:06.006000 milliseconds are missing.

yyyy-MM-dd HH:mm:ss.SSS is the format printed

Sysout value: From bean>>2016-01-30 11:43:06.006

What should I do to get the complete timestamp? I need this data to create filenames for report generation.

HebeleHododo
  • 3,620
  • 1
  • 29
  • 38
Banu Suruliraj
  • 11
  • 1
  • 2
  • 1
    Show us the code you are using to print the timestamp in Java. I'm not convinced that the precision has actually been lost. – Tim Biegeleisen Dec 28 '16 at 10:15
  • What version of DB2? What version of Java? I know that on IBM i, certain methods of retrieving the timestamp only returns milliseconds (first three decimal positions), and microseconds (positions 4-6) are returned as 0. – jmarkmurphy Dec 28 '16 at 14:20
  • It appears from http://stackoverflow.com/questions/33472569/any-new-method-to-get-current-time-with-accuracy-in-microseconds-in-java-now that even up to Java 8, the Java Clock class is only accurate up to milliseconds (3 digits). so what is in the DB? What is loading the DB field. Even though the field can hold microseconds, the process loading the field must be able to provide the microseconds.DB2 for i (at least at v7.1+) can provide microseconds using now(), CURRENT_TIME, or CURRENT_TIMESTAMP. I can't test any earlier right now. – jmarkmurphy Dec 28 '16 at 14:44

1 Answers1

1

Your Question is quite confusing, not sure if you are capturing the current time in Java or in your database. Either way, you are using a troublesome old date-time class (java.sql.Timestamp) that is now legacy, supplanted by java.time classes.

The java.time classes use a resolution of nanoseconds, for up to nine decimal digits of a fractional second. In any version of Java, you can carry a nanosecond value. However, capturing the current moment is a different story.

  • Java 8
    Capturing the current moment in Java 8 is limited to milliseconds, for three digits of resolution.

  • Java 9 and later
    Capturing the current moment in Java 9 uses a fresh implementation of java.time.Clock with finer resolution, depending on the limits of your host computer hardware of course.

On macOS Sierra running on a MacBook Pro with Intel i7 chip using Oracle Java 9.0.4, I am capturing the current moment in microseconds for six digits of decimal fraction.

Instant.now()

2018-03-09T21:03:33.831515Z

With a JDBC driver supporting JDBC 4.2 and later, you should be able to directly exchange java.time objects with your database. No need to ever use java.sql.Timestamp again.

If your DB2 column lacks time zone or offset-from-UTC info, use LocalDateTime class in Java.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

If your database column is in UTC, use Instant class.

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

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?

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154