Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-796

Avatica remote service truncates java.sql.Timestamp to milliseconds

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      TypedValue in Avatica read/writes java.sql.Timestamp in milliseconds even though natively the type supports nanosecond precision (and Phoenix does use the full precision). The JSON serialization protocol should count with this.

      I'd suggest serializing java.sql.Timestamp with `toString()` and deserializing with `valueOf()` if it's a string. Alternatively, it could be stored as a decimal number or just total number of nanoseconds.

        Activity

        Hide
        lukaslalinsky Lukas Lalinsky added a comment -

        Also, I'm happy to do the changes for this and the other tickets I have opened. I just need some input on whether it's actually something that you want done.

        Show
        lukaslalinsky Lukas Lalinsky added a comment - Also, I'm happy to do the changes for this and the other tickets I have opened. I just need some input on whether it's actually something that you want done.
        Hide
        julianhyde Julian Hyde added a comment -

        Agreed, we should do this.

        How many total bits do we need for a Timestamp? I reckon 96 bits. 64 for the millis since epoch, and 32 for nanos (20 would be sufficient, since we need a value between 0 and 999,999.) So, storing in a Java long is not an option.

        Using Timestamp.toString() will not work: it will format the value in the JVM's timezone, whereas SQL timestamp values are zoneless. For example, the value Timestamp(0) must be transmitted as "1970-01-01 00:00:00" (and extra digits after a decimal point if you like) in all locales.

        JSON has just has a "number" type which, if I understand the specification, is capable of transmitting integers larger than 2 ^ 63 losslessly. Maybe we could have a Jackson serializer that converts a Timestamp to a JSON 94 bit integer and a deserializer that reverses the process.

        Show
        julianhyde Julian Hyde added a comment - Agreed, we should do this. How many total bits do we need for a Timestamp? I reckon 96 bits. 64 for the millis since epoch, and 32 for nanos (20 would be sufficient, since we need a value between 0 and 999,999.) So, storing in a Java long is not an option. Using Timestamp.toString() will not work: it will format the value in the JVM's timezone, whereas SQL timestamp values are zoneless. For example, the value Timestamp(0) must be transmitted as "1970-01-01 00:00:00" (and extra digits after a decimal point if you like) in all locales. JSON has just has a "number" type which, if I understand the specification, is capable of transmitting integers larger than 2 ^ 63 losslessly. Maybe we could have a Jackson serializer that converts a Timestamp to a JSON 94 bit integer and a deserializer that reverses the process.
        Hide
        lukaslalinsky Lukas Lalinsky added a comment -

        I don't believe JSON has any strictly defined limits on integer sizes, so serializing it into a 96 bit integer would be fine. The reason I suggested the decimal number is because that way clients could send just the integer part, which could be defined as UNIX timestamp and the fractional part could be optional. But that's probably slightly biased point of view, because that's how timestamps typically work in Python.

        Show
        lukaslalinsky Lukas Lalinsky added a comment - I don't believe JSON has any strictly defined limits on integer sizes, so serializing it into a 96 bit integer would be fine. The reason I suggested the decimal number is because that way clients could send just the integer part, which could be defined as UNIX timestamp and the fractional part could be optional. But that's probably slightly biased point of view, because that's how timestamps typically work in Python.
        Hide
        julianhyde Julian Hyde added a comment -

        Similar remarks to CALCITE-795 on type system. I think we should know what the type of the column or parameter is before transmitting. If it is a timestamp(3), (i.e. only down to millseconds), don't transmit the nanos. If is it timestamp(9) (or precision between 4 and 9) then transmit the nanos.

        Show
        julianhyde Julian Hyde added a comment - Similar remarks to CALCITE-795 on type system. I think we should know what the type of the column or parameter is before transmitting. If it is a timestamp(3), (i.e. only down to millseconds), don't transmit the nanos. If is it timestamp(9) (or precision between 4 and 9) then transmit the nanos.

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            lukaslalinsky Lukas Lalinsky
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development