JDBC timestamp escape format and SimpleDateFormat in Java
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));
ejboy
Experienced Software Engineer. Creator of Scriptella ETL, an open source Java ETL tool.
Updated on June 04, 2022Comments
-
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 formatyyyy-mm-dd hh:mm:ss.fffffffff
, whereffffffffff
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 is427
.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)
andnew 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 over 11 yearsThis does not work. The problem is that SSS consumes the whole part including zeros, not just the first 3 digits.
-
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 over 11 yearsUnfortunately, these patterns do not cover my case. I've clarified the question. Please use example as a reference.
-
ejboy over 11 yearsYour 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 over 11 years@ejboy: Hmm. Looking. It looks like it's the extra 0s which are causing the problem.
-
ejboy over 11 yearsProposed pattern does not cause an exception, but the date is not parsed correctly. Input string
2012-02-05 17:00:34.427000000
will give2012-02-10 15:37:14.000
as the result -
ejboy over 11 yearsUnfortunately this does not answer my question, because I need
SimpleDateFormat
pattern. The date can be easily parsed usingjava.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 over 11 yearsIt's the trailing zeros after
.427
. I guess you have to substring your date text before parsing it inDateFormat
. -
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 about 9 yearsGood 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 about 9 yearsThe 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 about 9 yearsOh, 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 about 9 yearsWe do indeed now have such a class as you suggest:
Instant
, part of the java.time package built into Java 8. AnInstant
has nanoseconds resolution, so no loss of fractional seconds. The java.sql.Timestamp class has been expanded to now offertoInstant
andfrom
(Instant) methods. So you can use existing JDBC drivers. -
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.