From the Postgres Database I am getting a 6 digit microsecond (its actually a ) e.g. 2014-11-10 04:05:06.999999

now when i apply a date format of "yyyy-MM-dd HH:mm:ss.SSS" it converts the 999999 to corresponding seconds/minutes resulting in incorrect date. See code snippet below

String dt = "2014-11-10 04:05:06.999999";

String timeseriesFormat = "yyyy-MM-dd HH:mm:ss.SSS";

SimpleDateFormat dateFormat = new SimpleDateFormat(timeseriesFormat);

Date date = dateFormat.parse(dt);

System.out.println(dateFormat.format(date));

results in 2014-11-10 04:21:45.999

I want to rather truncate the last 3 digits and keep a date of 2014-11-10 04:05:06.999.

How to truncate it? I do not want to use any framework like joda etc.

解决方案

java.sql.Timestamp

You should be using your JDBC driver to get a java.sql.Timestamp object. That class is a hack, but it works. It is a java.util.Date but tracks the fractional seconds to resolution of nanoseconds instead of milliseconds. So it will preserve the microseconds used by Postgres.

You can use it as a java.util.Date but in other contexts you'll lose your extra resolution.

java.time

Tip: look at the java.time package built into Java 8. It provides a full date-time library whose objects have nanosecond resolution. The java.sql.Timestamp class has methods to convert back and forth.

Note that in java.time a formatter coded parsing pattern string does not work for a variable number of fractional second digits. See this Question, JSR-310 - parsing seconds fraction with variable length for discussion. The solution is to use a DateTimeFormatterBuilder object to say that we expect any number of zero, one, or more digits (up to nine) for a fractional second.

String input = "2014-11-10 04:05:06.999999";

DateTimeFormatter formatter = new DateTimeFormatterBuilder ()

.appendPattern ( "yyyy-MM-dd HH:mm:ss" )

.appendFraction ( ChronoField.NANO_OF_SECOND , 0 , 9 , true ) // Nanoseconds = 0-9 digits of fractional second.

.toFormatter ();

Our input string has no time zone nor offset-from-UTC. So we must first process as a "local" date-time, not tied to a moment on the timeline.

LocalDateTime localDateTime = LocalDateTime.parse ( input , formatter );

Let's tie it to the time line by applying an offset-from-UTC. I will assume that in the case of this Question the intended offset was UTC itself.

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

Finally we want to convert into a java.sql type for actually communicating with the database. To that we must get an Instant, a moment on the timeline, always in UTC by definition. You can think of an OffsetDateTime as being an Instant plus an offset-from-UTC. To convert to java.sql we need to strip away the offset-from-UTC component by extracting just a simple Instant.

Instant instant = odt.toInstant ();

java.sql.Timestamp ts = java.sql.Timestamp.from ( instant );

Dump to console.

System.out.println ( "input: " + input + " | localDateTime: " + localDateTime + " | odt: " + odt + " | instant: " + instant + " | ts: " + ts );

input: 2014-11-10 04:05:06.999999 | localDateTime: 2014-11-10T04:05:06.999999 | odt: 2014-11-10T04:05:06.999999Z | instant: 2014-11-10T04:05:06.999999Z | ts: 2014-11-09 20:05:06.999999