Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-31457

Analytics Doesn't Support Fractional Milliseconds For ISO8601 Strings

    XMLWordPrintable

Details

    • Untriaged
    • Unknown
    • CX Sprint 122, CX Sprint 123, CX Sprint 124, CX Sprint 125, CX Sprint 126, CX Sprint 127, CX Sprint 128, CX Sprint 129, CX Sprint 130, CX Sprint 131, CX Sprint 132, CX Sprint 135, CX Sprint 136, CX Sprint 137

    Description

      On 6.0 beta, if you supply an ISO8601 string that has fractionals of a millisecond to a temporal analytics function, the result is always null. This is different from the N1QL query behavior, which accepts the fractions of a millisecond.

      This is problematic for stored date/times that have these fractions. For example, in C# DateTime.Now when rendering as ISO8601 using ToString("O") will almost always have fractions of a millisecond. So storing something like a created timestamp on a document will then be mostly unusable in analytics.

      Example query:
      SELECT VALUE STR_TO_MILLIS("2018-09-29T10:29:18.123123-04:00")
      Returns:
      null

      The same query in N1QL returns (simply stripping the fractions of a millisecond):
      1538231358123

      This has been observed to affect DATE_DIFF_STR and DATE_ADD_STR as well.

      Attachments

        Issue Links

          Activity

            Analytics supports only millisecond precision, so up to 3 digits after the decimal point:

            SELECT VALUE str_to_millis("2018-09-29T10:29:18.123-04:00")
            -->
            1538231358123
            

            As a workaround if input timestamp has more than 3 decimal digits, then the following regular expression could be used to remove the remaining ones:

            SELECT VALUE str_to_millis(regexp_replace("2018-09-29T10:29:18.123123-04:00", "(\\.\\d{3})\\d+", "$1"))
            

            dmitry.lychagin Dmitry Lychagin (Inactive) added a comment - - edited Analytics supports only millisecond precision, so up to 3 digits after the decimal point: SELECT VALUE str_to_millis("2018-09-29T10:29:18.123-04:00") --> 1538231358123 As a workaround if input timestamp has more than 3 decimal digits, then the following regular expression could be used to remove the remaining ones: SELECT VALUE str_to_millis(regexp_replace("2018-09-29T10:29:18.123123-04:00", "(\\.\\d{3})\\d+", "$1"))

            Build couchbase-server-6.5.0-2231 contains cbas-core commit 7d5831b with commit message:
            MB-31457: Tests to check for fractional millisecond.

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-2231 contains cbas-core commit 7d5831b with commit message: MB-31457 : Tests to check for fractional millisecond.

            Closing based on Till's comments and validation done earlier.

            mihir.kamdar Mihir Kamdar (Inactive) added a comment - Closing based on Till's comments and validation done earlier.

            Build couchbase-server-6.6.2-9599 contains cbas-core commit 7d5831b with commit message:
            MB-31457: Tests to check for fractional millisecond.

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.2-9599 contains cbas-core commit 7d5831b with commit message: MB-31457 : Tests to check for fractional millisecond.

            People

              sandeep.gupta Sandeep Gupta (Inactive)
              btburnett3 Brant Burnett
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty