5

I'm trying to come up with SimpleDateFormat pattern to parse and format JDBC timestamps, in particular dates in the following format yyyy-mm-dd hh:mm:ss.fffffffff, where ffffffffff indicates nanoseconds.

Unfortunately new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss.SSS000000") does not work, the following exception is thrown: 

java.text.ParseException: Format.parseObject(String) failed

Is this possible with SimpleDateFormat at all, or I have to create a custom subclass of java.text.DateFormat to handle this case? Please note that it's not a question on how to parse yyyy-mm-dd hh:mm:ss.fffffffff string in Java, I'm interested in a declarative approach, i.e. SimpleDateFormat pattern which does not require additional modifications of the input string .

Example:

I expect the input 2012-02-05 17:00:34.427000000 to be parsed as java.util.Date where milliseconds part is 427.

Here is a list of formats I've tried so far and they all failed for various reasons:

  • new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss.SSS000000") - java.text.ParseException: Format.parseObject(String) failed
  • Both new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSSSSS", Locale.US) and new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.US) - are parsed as Fri Feb 10 15:37:14 rather than expected one Sun Feb 05 17:00:34. (The nanoseconds part of 427000000 is treated as milliseconds, even if only SSS is specified)
ejboy
  • 4,081
  • 5
  • 30
  • 32
  • 1
    "Does not work" is very vague - what happens? – Jon Skeet Jul 31 '12 at 12:59
  • http://stackoverflow.com/questions/6038136/how-do-i-parse-rfc-3339-datetimes-with-java – Markus Mikkolainen Jul 31 '12 at 13:02
  • The following exception is thrown: `java.text.ParseException: Format.parseObject(String) failed` – ejboy Jul 31 '12 at 13:03
  • I'm basically interested in parsing java.sql.Timestamp dates in this format `yyyy-mm-dd hh:mm:ss.fffffffff`, where `ffffffffff` indicates nanoseconds. – ejboy Jul 31 '12 at 13:20
  • 1
    @ejboy: And you want all the information down to the nanosecond? You didn't mention that *at all* in your question. Please read http://tinyurl.com/so-hints – Jon Skeet Jul 31 '12 at 13:24
  • @Jon:I'm sorry for the confusion. I've updated the question. – ejboy Jul 31 '12 at 13:58
  • why not just use `java.sql.Timestamp.valueOf (theTimestampString);` to parse JDBC timestamps ? – LiuYan 刘研 Jul 31 '12 at 14:09
  • @LiuYan I'm writing a Java library which internally uses MessageFormat for parsing, and I'm trying to figure out if it is possible to express JDBC timestamps in SimpleDateFormat syntax. Otherwise I have to go with creating a custom java.text.DateFormat adapter using java.sql.Timestamp. – ejboy Jul 31 '12 at 14:29

3 Answers3

6

I know this is a late answer, but I have been looking into a similar problem and I came across this question.

The issue here is that SimpleDateFormat is expecting a java.util.Date, which does not have a nanosecond field. So, it does not handle that case. The java.sql.Timestamp documentation says that it is a composite of java.util.Date and a nanosecond field. The date/time value to the second is stored in the Date portion of the object. Meaning that the remaining milliseconds can be ignored in favor of the nanosecond field. In my opinion, this is bad design because java.sql.Timestamp is a direct subclass of java.util.Date.

In any case, to answer the question, I don't think that it is possible to print the Timestamp with nanoseconds without appending to the String produced by SimpleDateFormat.

Here is one way to do it:

    Timestamp timestamp = new Timestamp(System.currentTimeMillis());
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss.");
    format.setTimeZone(TimeZone.getDefault()); // Set the TimeZone to whatever you are expecting.

    System.out.println(format.format(timestamp) + String.format("%09d", timestamp.getNanos());

EDIT:

Added padding for the nanoseconds String value.

minus
  • 320
  • 1
  • 5
  • 14
  • Good answer. And, yes, java.sql.Timestamp is a hack. All the old date-time classes (java.util.Date, .Calendar, SimpleDateFormat) are a confusing mess. – Basil Bourque Sep 04 '14 at 19:08
  • [The java.sql.Timestamp doc](http://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html) actually notes the hacky nature: `Due to the differences between the Timestamp class and the java.util.Date class mentioned above, it is recommended that code not view Timestamp values generically as an instance of java.util.Date. The inheritance relationship between Timestamp and java.util.Date really denotes implementation inheritance, and not type inheritance.` – Basil Bourque Sep 04 '14 at 21:59
  • Oh, I didn't see that. In that case it would make more sense to create a SQL timestamp class that does not inherit from java.util.Date. It could wrap a java.util.Date internally if necessary. – minus Sep 04 '14 at 22:25
  • We do indeed now have such a class as you suggest: [`Instant`](https://docs.oracle.com/javase/8/docs/api/java/time/Instant.html), part of the [java.time package](https://docs.oracle.com/javase/tutorial/datetime/index.html) built into Java 8. An `Instant` has [nanoseconds](https://en.wikipedia.org/wiki/Nanosecond) resolution, so no loss of fractional seconds. The java.sql.Timestamp class has been expanded to now offer [`toInstant`](https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html#toInstant--) and `from` (Instant) methods. So you can use existing JDBC drivers. – Basil Bourque Nov 21 '14 at 22:18
  • @Basil_Bourque That is a much needed improvement; thanks for pointing it out. However, the extent to which you can use existing JDBC drivers with this feature depends on which version of JDBC they support and how careful you want to be when you write your JDBC application. For example, you would be able to use a driver that supports JDBC 4.1, but since you would be compiling with Java 8 to get that feature, you would have to be careful not to call any methods that were introduced in 4.2. – minus Nov 24 '14 at 22:58
1

EDIT: Now that you've given us a data sample, it's reasonably simple:

"yyyy-MM-dd HH:mm:ss.SSS"

That certainly parses the value you've given. You may want to explicitly specify Locale.US as well, just so it doesn't try to use different separators...

EDIT: The trailing data beyond milliseconds causes issues. However, the early part of the data is fixed length (23 characters, I believe) so you should be able to write:

Date date = format.parse(text.substring(0, 23));
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • This does not work. The problem is that SSS consumes the whole part including zeros, not just the first 3 digits. – ejboy Jul 31 '12 at 13:11
  • @ejboy: Well if you could give us some test data and explain what you want to happen vs what *does* happen, it would be considerably easier to help you. – Jon Skeet Jul 31 '12 at 13:23
  • Your answer does not give a correct result. Check `new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.US)` it returns Feb _10 15:37:14_ for my input. – ejboy Jul 31 '12 at 15:33
  • @ejboy: Hmm. Looking. It looks like it's the extra 0s which are causing the problem. – Jon Skeet Jul 31 '12 at 15:40
  • Unfortunately this does not answer my question, because I need `SimpleDateFormat` pattern. The date can be easily parsed using `java.sql.Timestamp.valueOf (str)` but I'm interested in SimpleDateFormat pattern ,which can be used in MessageFormat'ed strings without modifying the input string. – ejboy Jul 31 '12 at 15:49
  • 2
    @magiconair: It did when it was asked. The OP only added the requirement of "without modifying the input string" later. – Jon Skeet Jun 04 '13 at 15:05
0

Date pattern for RFC3339 is of format:

yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

Unless you have a different date format coming from a resource provider.'

If you have fractional seconds:

yyyy-MM-dd'T'HH:mm:ss.SSSSSS'Z'

EDIT: Based on your example, the format I got to parse your string is:

DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSSSSS");
Buhake Sindi
  • 87,898
  • 29
  • 167
  • 228
  • Unfortunately, these patterns do not cover my case. I've clarified the question. Please use example as a reference. – ejboy Jul 31 '12 at 13:46
  • Proposed pattern does not cause an exception, but the date is not parsed correctly. Input string `2012-02-05 17:00:34.427000000` will give `2012-02-10 15:37:14.000` as the result – ejboy Jul 31 '12 at 15:45
  • It's the trailing zeros after `.427`. I guess you have to substring your date text before parsing it in `DateFormat`. – Buhake Sindi Jul 31 '12 at 16:09