ResultSet.getTimestamp() truncating trailing zeros in milliseconds

[cembry]

ResultSet.getTimestamp() seems to be truncating trailing zeros in the
milliseconds field.

I have a timestamp value in the database that shows as 2008-07-07
11:23:17.220 when I query it directly using MSSQL Management Studio. Note
that the milliseconds is 220. When I query that same column using JDBC and
then do ResultSet.getTimestamp(), it is showing the value as 2008-07-07
11:23:17.22. Note that the milliseconds is 22. This only occurs when the
milliseconds field in the database is divisible by ten and so ends with a
trailing zero. I can reproduce this 100% consistently.

This looks like a bug to me. It is causing one of my unit tests to fail. Am
I doing something wrong?

[joe.we...@gmail.com]

SQLServer doesn't actually support datetime values with a millisecond
accuracy,
so whatever round values to a reliable accuracy no better than what
the DBMS
supports, is better, IMHO.
Joe Weinstein

[cembry]

Thanks for the reply, but SQLServer is supposed to be accurate to within 3.33
milliseconds. That's quite a bit different than changing 220 milliseconds to
22 milliseconds. I'm not concerned with accuracy as much as just making sure
that events occur in the right sequential order. However, with this driver
problem, events that occur in order like this will be moved out of order.

actual timestamps in the database
1) 11:23:17.216
2) 11:23:17.220
3) 11:23:17.224

using the driver the events would change to this order if I compare timestamps
2) 11:23:17.22
1) 11:23:17.216
3) 11:23:17.224

[cembry]

After further investigation, it turns out the problem is only with the
toString method on java.sql.Timestamp. Unfortunately, for reasons specific to
my app, I was converting java.sql.Timestamp to java.util.Date by parsing
java.sql.Timestamp.toString() instead of using java.sql.Timestamp.getTime().
I'll just have to work around it.

[Evan T. Basalik (MSFT)]

Are you doing your sort before or after the string conversion? If you are doing it after, then I would not be surprised with the order you got below.

If you are doing the sort order before, can you provide a code sample that demonstrates the problem?

Evan
--------------------
>Thread-Topic: ResultSet.getTimestamp() truncating trailing zeros in millisec
>thread-index: AcjhDfSuNmV8xdXrTRaojCAl2zjHTQ==
>X-WBNR-Posting-Host: 65.55.21.8
>From: =?Utf-8?B?Y2VtYnJ5?= <cem...@discussions.microsoft.com>
>References: <CC966296-660B-434A...@microsoft.com> <c6d617c9-b0f4-41d8-b0bb-fbd2ae9480e9@
25g2000hsx.googlegroups.com>
>Subject: Re: ResultSet.getTimestamp() truncating trailing zeros in millisec
>Date: Tue, 8 Jul 2008 08:19:03 -0700
>Lines: 15
>Message-ID: <20AC4226-749B-4AD6...@microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: TK2MSFTNGHUB02.phx.gbl
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:539
>NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver

Evan T. Basalik
This posting is provided “AS IS” with no warranties, and confers no rights.

[cembry]

Here is a code sample to illustrate the problem.

First prep the table:
update VOLSS_customer_persons
set vcp_last_pw_change = 'Jan 1, 1970 00:00:00.990', vcp_last_login = 'Jan
1, 1970 00:00:00.987'
where vcp_id = 22624

/** These are the classes I'm using **/
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

Here's the code. I'm running it as a JUnit test case.

public void testSomething() throws Exception {
/**
* get a connection to your db first
**/
Connection con = db.getConnection();

// everything below here can be used verbatim
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select vcp_last_login as milli987,
vcp_last_pw_change as milli990 from VOLSS_customer_persons where vcp_id =
22624");
Timestamp milli987 = null;
Timestamp milli990 = null;
while (rs.next()) {
milli987 = rs.getTimestamp("milli987");
milli990 = rs.getTimestamp("milli990");
}
rs.close();
stmt.close();
con.close();
// should end with .987 and does
System.out.println("milli987 = " + milli987.toString());
// should end with.990, but instead shows .99
System.out.println("milli990 = " + milli990.toString());

/**
* Here's where the bug can get you into trouble
*/
String DB_TIMESTAMP_FORMAT = "yyyy-MM-dd kk:mm:ss.SSS";
SimpleDateFormat dateParser = new
SimpleDateFormat(DB_TIMESTAMP_FORMAT);
Date dtMilli987 = dateParser.parse(milli987.toString());
Date dtMilli990 = dateParser.parse(milli990.toString());

System.out.println("dtMilli987 = " + dateParser.format(dtMilli987));
System.out.println("dtMilli990 = " + dateParser.format(dtMilli990));

/**
* Jan 1, 1970 00:00:00.990 should be after Jan 1, 1970
00:00:00.987, but the assertion
* fails because the trailing 0 was removed from 990
*/

assertTrue(dtMilli990.after(dtMilli987));
}

[Evan T. Basalik (MSFT)]

I do see what you mean with regard to dropping the trailing zero, but it looks like there is a more serious problem with the SimpleDateFormat class. This is
the output I get:

milli987 = 1970-01-01 00:00:00.987
milli990 = 1970-01-01 00:00:00.99
dtMilli987 = 1970-01-01 24:00:00.987
dtMilli990 = 1970-01-01 24:00:00.099 <--How did .99 become .099??!?!?!

As you can see, 0.99 (which is functionally equivalent to 0.990) somehow got parsed to 0.099. That is completely wrong.

Even worse, the problem does not appear to be isolated to the JDBC driver. I can replicate the problem just trying to parse a string:

Date dtString = dateParser.parse("1970-01-01 00:00:00.99") outputs "1970-01-01 24:00:00.099"

It looks like the parser is using the periods are delimiters. I don't know the specification well enough to know if you are required to have the three digits after
the period, but I certainly don't think it should be the case if it is.

Are you sure your regression test is failing due to the loss of the trailing zero? Or, is it failing because of the bad parsing?

Either way, I found these to be comparison that works successfully

System.out.println("990 after 987? = " + milli990.after(milli987)) -> TRUE
System.out.println("987 after 990? = " + milli987.after(milli990)); -> FALSE

Evan

--------------------
>Thread-Topic: ResultSet.getTimestamp() truncating trailing zeros in millisec

>thread-index: AcjhUCsv49I9d9UxS7qJP0G8lVLx/Q==
>X-WBNR-Posting-Host: 207.46.19.168

>From: =?Utf-8?B?Y2VtYnJ5?= <cem...@discussions.microsoft.com>
>References: <CC966296-660B-434A...@microsoft.com> <c6d617c9-b0f4-41d8-b0bb-fbd2ae9480e9@

25g2000hsx.googlegroups.com> <20AC4226-749B-4AD6...@microsoft.com> <vLDxOFU4...@TK2MSFTNGHUB02.phx.gbl>

>Subject: Re: ResultSet.getTimestamp() truncating trailing zeros in millisec

>Date: Tue, 8 Jul 2008 16:13:01 -0700
>Lines: 68
>Message-ID: <F552D550-45FF-4A63...@microsoft.com>

>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: TK2MSFTNGHUB02.phx.gbl

>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:544

>NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>

Evan T. Basalik

[cembry]

Thanks for looking at this for me. In my opinion 3 digits after the period
should definitely be required for a valid timestamp. The period is confusing
because it makes us think of decimal numbers which would be fine to use
1970-01-01 00:00:00.99 if it was a decimal. But it's not a decimal. It is
just a delimiter before the milliseconds. It's just like we would never write
two minutes and four seconds after 1 am as 1:2:4 am. By convention we always
pad with zeros to keep the same number of places between the delimiters.
01:02:04 am. That's the same thing SimpleDateFormat is doing with the
incomplete .99 value. It's padding it with an extra 0 to make .099.

[Evan T. Basalik (MSFT)]

We store and transmit the DateTime object as a binary representation, so the fact that the toString method drops the trailing zero would appear to be a bug
in the toString implementation. It is highly unlikely that we would just drop the trailing zero if we were messing up the binary data - instead, I would expect to
see a completely different time representation.

Also, the fact that I can replicate the parsing issue with the input tstring would also imply a pretty severe problem with the parsing logic. It should not rely on
three digits after the decimal if it will accept string input (since string input can come from many sources).

I really think your issue has shown two bugs (one very severe) with the Java calls themselves, rather than any indications of problems with the driver.

Evan
--------------------
>Thread-Topic: ResultSet.getTimestamp() truncating trailing zeros in millisec

>thread-index: Acj7zJHAwbRG5jj4RrywH2xwvl1j/g==
>X-WBNR-Posting-Host: 207.46.193.207

>From: =?Utf-8?B?Y2VtYnJ5?= <cem...@discussions.microsoft.com>
>References: <CC966296-660B-434A...@microsoft.com> <c6d617c9-b0f4-41d8-b0bb-fbd2ae9480e9@
25g2000hsx.googlegroups.com> <20AC4226-749B-4AD6...@microsoft.com> <vLDxOFU4...@TK2MSFTNGHUB02.phx.gbl>

<F552D550-45FF-4A63...@microsoft.com> <jNuOtOy#IHA....@TK2MSFTNGHUB02.phx.gbl>

>Subject: Re: ResultSet.getTimestamp() truncating trailing zeros in millisec

>Date: Mon, 11 Aug 2008 09:09:01 -0700
>Lines: 9
>Message-ID: <5BC77205-FAD6-40B7...@microsoft.com>

>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal

>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3119
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: TK2MSFTNGHUB02.phx.gbl
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:566

>NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>

Evan T. Basalik

[cembry]

Thank you. You are correct. I created some Timestamps from scratch and the
problem is there in the toString method.