JPA truncates nanoseconds when using date criteria & SQL datetime2

Question

I'm trying to create a where clause involving a SQL Server datetime2 field (accurate to 100 nanoseconds); using JPA & Hibernate.

My code looks something like this:

CriteriaBuilder cb = em.getCriteriaBuilder();
List<Predicate> predicates = new ArrayList<>();
CriteriaQuery(X) q = cb.createQuery(X.class);
Root<X> root = q.from(X.class);

java.sql.Timestamp mySqlTimeStamp = Timestamp.valueOf("2015-06-04 11:31:53.2119339");

predicates.add(cb.greaterThan(root.get("DateModified"), mySqlTimeStamp))

q.where(predicates.toArray(new Predicate[predicates.size()]));

em.createQuery(q).getResultList();

SQL Server Profiler reveals the timestamp parameter value is truncated to 2015-06-04 11:31:53.210 - weird, that's not even a rounding. Needles to say, i have inaccuracies in the result set.

If i manually change the param to the full value 2015-06-04 11:31:53.2119339 all is good.

Question is, how to get JPA to not truncate the date?

Alternatively, how to inject my own parameter value serializer for my timestamp fields?

Help appreciated; thanks

UPDATE I've tracked it to this jtds jdbc code: net.sourceforge.jtds.jdbc.JtdsPerparedStatement:

protected void setParameter(int parameterIndex, Object x, int targetSqlType, int scale, int length)
        throws SQLException {
        ParamInfo pi = getParameter(parameterIndex);

        if ("ERROR".equals(Support.getJdbcTypeName(targetSqlType))) {
            throw new SQLException(Messages.get("error.generic.badtype",
                                Integer.toString(targetSqlType)), "HY092");
        }

        // Update parameter descriptor
        if (targetSqlType == java.sql.Types.DECIMAL
            || targetSqlType == java.sql.Types.NUMERIC) {

            pi.precision = connection.getMaxPrecision();
            if (x instanceof BigDecimal) {
                x = Support.normalizeBigDecimal((BigDecimal) x, pi.precision);
                pi.scale = ((BigDecimal) x).scale();
            } else {
                pi.scale = (scale < 0) ? TdsData.DEFAULT_SCALE : scale;
            }
        } else {
            pi.scale = (scale < 0) ? 0 : scale;
        }

        if (x instanceof String) {
            pi.length = ((String) x).length();
        } else if (x instanceof byte[]) {
            pi.length = ((byte[]) x).length;
        } else {
            pi.length   = length;
        }

        if (x instanceof Date) {
            x = new DateTime((Date) x);
        } else if (x instanceof Time) {
            x = new DateTime((Time) x);
        } else if (x instanceof Timestamp) {
            x = new DateTime((Timestamp) x);
        }

        pi.value = x;
        pi.jdbcType = targetSqlType;
        pi.isSet = true;
        pi.isUnicode = connection.getUseUnicode();
    }
}

Where the timestamp is forced to net.sourceforge.jtds.jdbc.DateTime type, which supports milliseconds only.

I guess you mean "Timestamp.valueOf(...)" instead of new. I did a similar thing using H2 database and DataNucleus JPA and my nanos are passed through to the datastore. Perhaps see what Hibernate is putting in the log and what the SQLServer JDBC driver is doing? — Neil Stockton, Jun 04 '15 at 14:23
Yes, valueOf() - I've corrected the code. I'm not passing nanos yet wrt updates etc - but am reading them successfully using JPA. Did you get success with parameters in criteria? — Gilbert, Jun 04 '15 at 15:41
You mean, in the Criteria above, just put a cb.parameter and then call setParameter on the query (instead of using a literal) ? Yes that worked for me with H2+DataNucleus — Neil Stockton, Jun 04 '15 at 15:45

score 0 · Accepted Answer · answered Jun 12 '15 at 04:22

This is more of a workaround; its what I did to make the problem go away:

As stated in the question update, it was jtds drivers that do not support datetime2 parameters. I simply shifted to Microsoft drivers - problem solved.

Some further workaround ideas:

Embed a Convert() function call, wrapping the parameter literal; send the Timestamp string value.
Fix jtds code - clearly jdbc supports nanos

JPA truncates nanoseconds when using date criteria & SQL datetime2

1 Answers1