1

I am using the following statement to obtain a timestamp from my SQL DB:

stmt.setTimestamp(i++, new java.sql.Timestamp(example.getExampleDate().getTime()))

Which works just fine and returns:

2013-02-22 12:27:34.0

Now it happens that I need it to be more precise, like this:

2013-02-22 12:27:34.000

So I found the following method in the docs which apparently is exactly what I want:

setNanos(int n)

Sets this Timestamp object's nanos field to the given value.

But I need to figure out how to include that into my prepared statement?

I tried for example

stmt.setTimestamp(i++, new java.sql.Timestamp(example.getExampleDate().getTime()).setNanos(3));

but than that returns the following error:

The method setTimestamp(int, Timestamp) in the type PreparedStatement is not applicable for the arguments (int, void)

Thanks alot for your help!

Community
  • 1
  • 1
bngschmnd
  • 111
  • 1
  • 10
  • Well, you set the nanos in the timestap, and then pass this timestap as parameter of the prepared statement. What's the concrete issue? – JB Nizet Nov 11 '16 at 12:21
  • How would that be done? – bngschmnd Nov 11 '16 at 12:23
  • 1
    `Timestamp timestamp = ...; timestamp.setNanos(234); stmt.setTimestamp(i++, timestamp);`. – JB Nizet Nov 11 '16 at 12:25
  • 34.000 is not more precise than 34.0, `.0` is simply what gets used when the millisecond fraction is 0. Also be aware that `getTime` only has millisecond precision. The nanos part is separate. Please describe a more specific use case. BTW: Is SQL Server actually capable of storing timestamps with nanosecond precision? – Mark Rotteveel Nov 11 '16 at 12:29
  • Yes, my Database has entries in the format stated above, with a millisecond fraction of 3. – bngschmnd Nov 11 '16 at 12:35
  • Thanks, JB, your comment did supply the information that i needed. Unfortunately when i use 'setNanos(000)' it gets again shortened to 34.0 – bngschmnd Nov 11 '16 at 12:56
  • That's basic arithmetic. The number 0 and the is the number 0 is the number 0. 000 is still 0. Just like 0.5 and 0.50 and 0.500 is the exact same number. What you want is not to change the value of the timestamp. What you want is to format it as a string in a different way, with the value 0 formatted as 000. setNanos() won't help. Concatenate "00" to the string you get when formatting it. – JB Nizet Nov 11 '16 at 13:29

1 Answers1

1

setNanos() returns void. So the expression new java.sql.Timestamp(example.getExampleDate().getTime()).setNanos(3) is of type void. You can't pass a void to the method setTimestamp(). You must pass a Timestamp.

So use a variable:

Timestamp timestamp = new Timestamp(example.getExampleDate().getTime());
timestamp.setNanos(3);
stmt.setTimestamp(i++, timestamp);
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • 1
    Important caveat from the [javadoc](http://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html): _"Note: This type is a composite of a java.util.Date and a separate nanoseconds value. Only integral seconds are stored in the java.util.Date component. The fractional seconds - the nanos - are separate."_ So calling `setNanos` will overwrite any subsecond value derived from the `getTime` value! – Mark Rotteveel Nov 11 '16 at 12:51
  • Your code returns 2011-10-21 11:38:40.000000003 for the timestamp. If I use timestamp.setNanos(234) it returns 2011-10-21 11:38:40.0000000234 – bngschmnd Nov 11 '16 at 12:59
  • That means it works fine: you have the right nanos value. What's the problem. What else did you expect? – JB Nizet Nov 11 '16 at 13:01
  • I need the value of nanos to be declared with 3 digits, even though they are zeros. not 2013-02-22 12:27:34.0 but 2013-02-22 12:27:34.000 – bngschmnd Nov 11 '16 at 13:21
  • Then write a method formatting the timestamp the way you want. Settings nanos to the timestamp won't do that. You don't want nanoseconds in your timestamp. You want the value 0 to be displayed as 000. – JB Nizet Nov 11 '16 at 13:24
  • Any suggestions for an approach to solve that problem? Because I think messing around with toString() methods is not a good idea here. – bngschmnd Nov 11 '16 at 13:38