Java date pattern for SQL date (ISO 9075)

Question

I am trying to parse an SQL date string (ISO 9075) and that uses microseconds(!) instead of milliseconds, for example

2010-11-22 08:08:08.123456

However, SimpleDateFormat refuses to recognize a pattern like "yyyy-MM-dd HH:mm:ss.SSSSSS" and "yyyy-MM-dd HH:mm:ss.SSS" does not work, either.

The code I am using looks something like this:

String dateString = "2010-11-22 08:08:08.123456";
String pattern = "yyyy-MM-dd HH:mm:ss.SSSSSS";

try
{
    format = new SimpleDateFormat(pattern);
    format.setLenient(false);
    position.setIndex(0);
    Date date1 = format.parse(dateString, position);
    System.out.println("Date 1: " + date1);
    Date date2 = format.parse(dateString);
    System.out.println("Date 2: " + date1);
}
catch (Exception e) // Should not happen
{
    e.printStackTrace();
}

Whichever of the 2 patterns (".SSS" or ".SSSSSS") I use, date1 is printer as null, whereas date2 causes a parsing exception (java.text.ParseException: Unparseable date).

Answer 1

tl;dr

Insert/Update.

myPreparedStatement.setObject( 
    … , 
    Instant.parse( "2010-11-22 08:08:08.123456".replace( " " , "T" ) + "Z" ) 
) ;

Retrieval.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;
String output = ldt.toString().replace( "T" , " " ) ;  // Remove the standard ISO 8601 format’s use of `T` in the middle with the SQL-style SPACE.

java.time

Java 8 brings the new java.time package, to supplant the notoriously troublesome old java.util.Date, .Calendar, and SimpleDateFormat classes. Those old legacy classes are limited to milliseconds resolution while your input string has microseconds. You cannot cram six decimal places into a data type limited to three.

The date-time values in java.time have nanosecond resolution, more than enough for your microseconds. That means up to nine decimal places for a fractional second.

With JDBC 4.2 and later, use the Instant class directly.

Parse your input String. To comply with ISO 8601 format used by default in the java.time classes, replace the SPACE in the middle with a T. Your given example must be coming from a column type TIMESTAMP WITHOUT TIME ZONE which means not a specific moment, not a point on the timeline.

String input = "2010-11-22 08:08:08.123456".replace( " " , "T" ) ;
LocalDateTime ldt = LocalDateTime.parse( input ) ;

And if this input was meant to be a moment in UTC, append a Z. Then you do have a moment, a specific point on the timeline. For use with a column type of TIMESTAMP WITH TIME ZONE.

String input = "2010-11-22 08:08:08.123456".replace( " " , "T" ) + "Z" ;
Instant instant = Instant.parse( input ) ;

Send to your database.

myPreparedStatement.setObject( … , instant ) ;

…and…

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?

• 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.

---

UPDATE: The Joda-Time project is now in maintenance mode, with the team advising migration to the java.time classes. Leaving this section intact as history.

Joda-Time

As noted above, you should avoid using the java.util.Date, .Calendar, and SimpleDateFormat classes. As of Java 8 you have the choice of using java.time or Joda-Time or both, as they each their strengths and weaknesses. Joda-Time also works in earlier versions of Java.

You can parse that string using Joda-Time.

Alternatively, if you have access to the java.sql.Timestamp that generated your string, you can simply pass the Timestamp object to the constructor of a DateTime. I suggest also passing a time zone as DateTime objects know their own assigned time zone.

DateTime dateTime = new DateTime( mySqlTimestamp, DateTimeZone.UTC );

or

DateTime dateTime = new DateTime( mySqlTimestamp, DateTimeZone.forID( "Europe/Paris" ) );

Joda-Time has a resolution of milliseconds. So either way you generate the DateTime (parsing or passing), you will loose the finer fraction of the second (ignored/truncated, not rounded).

Answer 2

Hmm. Given that Date doesn't have microsecond resolution, if you know that all the SQL date strings have the full six digits after the seconds decimal point (2010-11-22 08:08:08.000000 for example), why not just chop off the final three digits and use SimpleDateFormat on the remainder?

Answer 3

You may want to look at DATE4J which specifically tries to deal with database dates, to nanosecond precision.

Answer 4

You might want to call the DateFormat parse, because I think it will cut off the string. Try:

Date date1 = format.parse(dateString);

Plus, don't use "SSSSS". According to specs, only "SSS" is valid for dateformat.

Other than that, I agree with cutting it off or parsing in SQL.

Plus, you have setLenient to false, so it's strict. So the string, being longer is going to cause it to fail. Maybe that's why it returns null. Unsure, would have to test.

Answer 5

Maybe chop the remaining fraction part out of the dateString before parse the date? I have the following

    String dateString = "2010-11-22 08:08:08.123456";
    String fraction = dateString.substring(dateString.length() - 3);
    String formatString = dateString.substring(0, dateString.length() - 3);
    String pattern = "yyyy-MM-dd HH:mm:ss.SSS";
    ParsePosition position = new ParsePosition(0);

    try
    {

        SimpleDateFormat format = new SimpleDateFormat(pattern);
        format.setLenient(false);
        position.setIndex(0);
        Date date1 = format.parse(formatString, position);          
        System.out.println("Date 1: " + date1);
        System.out.println("Date 1 fraction: " + fraction);
        Date date2 = format.parse(formatString);
        System.out.println("Date 2: " + date2);
        System.out.println("Date 2 fraction: " + fraction);
    }
    catch (Exception e) // Should not happen
    {
        e.printStackTrace();
    }

This allow the date to parse until millisecond precision while you still retain the fraction micro part.

Answer 6

If you have control over the SQL date which is an input to the java code, you could try and retrieve the date from SQL in a format which will work ("yyyy-MM-dd HH:mm:ss.SSS").