JDBC timestamp escape format and SimpleDateFormat in Java

17,357

Solution 1

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.

Solution 2

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));
Share:
17,357
ejboy
Author by

ejboy

Experienced Software Engineer. Creator of Scriptella ETL, an open source Java ETL tool.

Updated on June 04, 2022

Comments

  • ejboy
    ejboy over 1 year

    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
    ejboy over 11 years
    This does not work. The problem is that SSS consumes the whole part including zeros, not just the first 3 digits.
  • Jon Skeet
    Jon Skeet over 11 years
    @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.
  • ejboy
    ejboy over 11 years
    Unfortunately, these patterns do not cover my case. I've clarified the question. Please use example as a reference.
  • ejboy
    ejboy over 11 years
    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.
  • Jon Skeet
    Jon Skeet over 11 years
    @ejboy: Hmm. Looking. It looks like it's the extra 0s which are causing the problem.
  • ejboy
    ejboy over 11 years
    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
    ejboy over 11 years
    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.
  • Buhake Sindi
    Buhake Sindi over 11 years
    It's the trailing zeros after .427. I guess you have to substring your date text before parsing it in DateFormat.
  • Jon Skeet
    Jon Skeet over 10 years
    @magiconair: It did when it was asked. The OP only added the requirement of "without modifying the input string" later.
  • Basil Bourque
    Basil Bourque about 9 years
    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
    Basil Bourque about 9 years
    The java.sql.Timestamp doc 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.
  • minus
    minus about 9 years
    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.
  • Basil Bourque
    Basil Bourque about 9 years
    We do indeed now have such a class as you suggest: Instant, part of the java.time package built into Java 8. An Instant has nanoseconds resolution, so no loss of fractional seconds. The java.sql.Timestamp class has been expanded to now offer toInstant and from (Instant) methods. So you can use existing JDBC drivers.
  • minus
    minus almost 9 years
    @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.