Uploaded image for project: 'Nuxeo Documentation'
  1. Nuxeo Documentation
  2. NXDOC-2616

Improve NXQL doc on DATE and TIMESTAMP regarding timezone

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2021, 2023
    • Fix Version/s: 2021, 2023
    • Component/s: None
    • Tags:
    • Team:
      PLATFORM
    • Sprint:
      nxplatform #100, nxplatform #101, nxplatform #102
    • Story Points:
      1

      Description

      The default NXQL DATE is taken as UTC but can also be used with a time and time zone to avoid weird behavior depending on user input time zone.

      For instance, if the user time zone (its browser) is set to UTC+2 and it sets a dc:expired to 2023-07-14, the date is stored as UTC 2023-07-13T22:00:00.

      The following query will not match because the 2023-07-14T00:00:00 != 2023-07-13T22:00:00

      SELECT * FROM Document WHERE dc:expired = DATE '2023-07-14'
      

      The following query will not match because the 2023-07-13T00:00:00 != 2023-07-13T22:00:00

      SELECT * FROM Document WHERE dc:expired = DATE '2023-07-13'
      

      But, you can pass an explicit date with time zone or even a timestamp to have the exact match:

      SELECT * FROM Note WHERE dc:expired = DATE '2023-07-13T22:00:00Z'
      SELECT * FROM Note WHERE dc:expired = DATE '2023-07-14T00:00:00+02:00'
      SELECT * FROM Note WHERE dc:expired = TIMESTAMP '2023-07-14T00:00:00+02:00'
      

      Also, note that the following is working on VCS because there is a DATE cast (failing on Mongo):

      SELECT * FROM Document WHERE DATE(dc:expired) = DATE '2023-07-13'
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: