OpenJPA
  1. OpenJPA
  2. OPENJPA-645

Date millisecond precision lost for Informix IDS and SQLServer

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 1.0.3, 1.1.0
    • Fix Version/s: 1.0.4, 1.2.0, 1.3.0, 2.0.0-M1
    • Component/s: jdbc
    • Labels:
      None

      Description

      An entity has an attribute of type java.util.Date, annotated with @Temporal(TemporalType.TIMESTAMP):

      @Temporal(TemporalType.TIMESTAMP)
      public Date udate;

      This gets mapped in Informix to a column of type:

      udate DATETIME YEAR TO FRACTION (3)

      and in SQLServer to

      udate DATETIME

      When the udate attribute is assigned a value with millisecond precision, say "12:34:56:789", OpenJPA chops off the millisecond fractional part when it generates the INSERT statement.

      In DBDictionary, for this type, we come to setDate() with the 'val' parameter set to the correct java.util.Date value "12:34:56:789". (The millisecond value is stored in the (Gregorian.Date) cdate.millis attribute of java.util.Date). setDate() then calls setTimestamp() - the last else - with a new instance of java.sql.Timestamp:

      setTimestamp(stmnt, idx, new Timestamp(val.getTime()), null, col);

      java.sql.Timestamp is made up of 2 parts - a date part that stores the time upto seconds, and a separate attribute, called nanos, that stores everything that is fractional of seconds.

      So the new Timestamp value that is sent to setTimestamp() has this:

      (Gregorian.Date) cdate = 12:34:56
      nanos = 789000000

      In setTimestamp() there is a check for supportsTimestampNanos. Because in the InformixDictionary and SQLServer dictionaries this is set to false, the code then zeros out the nanos field:

      if (supportsTimestampNanos)
      val.setNanos(nanos);
      else
      val.setNanos(0);

      Consequently, all fractional seconds information is lost for these 2 database types from the INSERT statement for this timestamp value.

      The nanos field in java.sql.Timestamp does not really mean that only nanoseconds are stored there - it means that any fractional value, after seconds will be stored there.This problem happens not only with the Date field in the entity, but also with java.util.Calendar and java.sql.Timestamp. The solution is to always set the nanoseconds value in the (java.sql.Timestamp)val field. The check for supportsTimestampNanos, as well as the flag itself, is not needed, because both IDS and SQLServer do allow fractional seconds.

      Will attach a patch ASAP. Albert has reviewed the proposed solution.

      1. patch-645.txt
        2 kB
        Dinkar Rao
      2. OPENJPA-645-1.0.x.patch
        2 kB
        B.J. Reed

        Activity

        Hide
        Catalina Wei added a comment -

        fix checked in under r672017

        Show
        Catalina Wei added a comment - fix checked in under r672017
        Hide
        B.J. Reed added a comment -

        In 1.0.x, SQL Server has already been updated to allow the fractional portion of seconds to be stored. The OPENJPA-645-1.0.x patch does the same for InformixDictionary and removes the flag from DBDictionary.

        Show
        B.J. Reed added a comment - In 1.0.x, SQL Server has already been updated to allow the fractional portion of seconds to be stored. The OPENJPA-645 -1.0.x patch does the same for InformixDictionary and removes the flag from DBDictionary.
        Hide
        Michael Dick added a comment -

        The changes predate 1.2.0 (1.2.x branch created under revision 680507). It's missed the release notes though and is certainly fixed in 2.0.0M1.

        Show
        Michael Dick added a comment - The changes predate 1.2.0 (1.2.x branch created under revision 680507). It's missed the release notes though and is certainly fixed in 2.0.0M1.

          People

          • Assignee:
            Unassigned
            Reporter:
            Dinkar Rao
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development