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

DATE_TRUNC_MILLIS result is incorrect as compared to N1QL.

    XMLWordPrintable

Details

    • Untriaged
    • No
    • CX Sprint 98, CX Sprint 114

    Description

      Reference: https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

      Query:

       SELECT DATE_TRUNC_MILLIS(1463284740000,'day') as day,      DATE_TRUNC_MILLIS(1463284740000,'month') as month,      DATE_TRUNC_MILLIS(1463284740000,'year') as year;


      Output:-
      CBAS:

      [
        {
          "day": 1463270400000,
          "month": 1462060800000,
          "year": 1451606400000
        }
      ]
      

      N1QL:

      [
        {
          "day": 1463270400000,
          "month": 1462147200000,
          "year": 1451696400000
        }
      ]
      

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          Build couchbase-server-6.5.0-1167 contains asterix-opt commit 2ed8f30 with commit message:
          MB-28998: date_trunc_millis() should truncate in UTC

          build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-1167 contains asterix-opt commit 2ed8f30 with commit message: MB-28998 : date_trunc_millis() should truncate in UTC

          Dmitry Lychagin,

          On build 6.0.0-1564 The result of the below query is same in N1QL and CBAS.

          SELECT DATE_TRUNC_MILLIS(1463284740000,'day') as day, DATE_TRUNC_MILLIS(1463284740000,'month') as month, DATE_TRUNC_MILLIS(1463284740000,'year') as year;

          We now see month and year aligned. Please, let us know if we can close the ticket.

          tanzeem.ahmed Tanzeem Ahmed (Inactive) added a comment - Dmitry Lychagin , On build 6.0.0-1564 The result of the below query is same in N1QL and CBAS. SELECT DATE_TRUNC_MILLIS( 1463284740000 , 'day' ) as day, DATE_TRUNC_MILLIS( 1463284740000 , 'month' ) as month, DATE_TRUNC_MILLIS( 1463284740000 , 'year' ) as year; We now see month and year aligned. Please, let us know if we can close the ticket.

          Tanzeem Ahmed I think we can close the ticket if in CBAS the function output is correct semantically. trunc to day should set all time components to 0 in UTC, right? If it does that then the CBAS output is semantically correct and the issue can be closed. What's the CBAS result you're getting on your system?

          dmitry.lychagin Dmitry Lychagin (Inactive) added a comment - Tanzeem Ahmed I think we can close the ticket if in CBAS the function output is correct semantically. trunc to day should set all time components to 0 in UTC, right? If it does that then the CBAS output is semantically correct and the issue can be closed. What's the CBAS result you're getting on your system?

          Dmitry Lychagin, my bad I was trying on my local Vagrants(UTC time) that was the reason for output's to be same. When I tried on VM(PDT) I get the below output. As you have mentioned in above comment there is no alignment in month and year. 

          SELECT DATE_TRUNC_MILLIS(1463284740000,'day') as day, MILLIS_TO_UTC(DATE_TRUNC_MILLIS(1463284740000,'month')) as month, MILLIS_TO_UTC(DATE_TRUNC_MILLIS(1463284740000,'year')) as year;
          --- Query
          [
          {
          "day": 1463270400000,
          "month": "2016-05-02T00:00:00Z",
          "year": "2016-01-02T01:00:00Z"
          }
          ]
           
          --- CBAS
          [
          {
          "day": 1463270400000,
          "month": "2016-05-01T00:00:00Z",
          "year": "2016-01-01T00:00:00Z"
          }
          ]
          

          tanzeem.ahmed Tanzeem Ahmed (Inactive) added a comment - Dmitry Lychagin , my bad I was trying on my local Vagrants(UTC time) that was the reason for output's to be same. When I tried on VM(PDT) I get the below output. As you have mentioned in above comment there is no alignment in month and year.  SELECT DATE_TRUNC_MILLIS( 1463284740000 , 'day' ) as day, MILLIS_TO_UTC(DATE_TRUNC_MILLIS( 1463284740000 , 'month' )) as month, MILLIS_TO_UTC(DATE_TRUNC_MILLIS( 1463284740000 , 'year' )) as year; --- Query [ { "day" : 1463270400000 , "month" : "2016-05-02T00:00:00Z" , "year" : "2016-01-02T01:00:00Z" } ]   --- CBAS [ { "day" : 1463270400000 , "month" : "2016-05-01T00:00:00Z" , "year" : "2016-01-01T00:00:00Z" } ]

          Tanzeem Ahmed I think we should close this issue. It's not a complete alignment, but CBAS results seem to be semantically correct.

          dmitry.lychagin Dmitry Lychagin (Inactive) added a comment - Tanzeem Ahmed I think we should close this issue. It's not a complete alignment, but CBAS results seem to be semantically correct.

          People

            tanzeem.ahmed Tanzeem Ahmed (Inactive)
            ritesh.agarwal Ritesh Agarwal
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty