-
Type: Improvement
-
Status: Resolved
-
Priority: Minor
-
Resolution: Fixed
-
Affects Version/s: 2021, 2023
-
Component/s: None
-
Tags:
-
Team:PLATFORM
-
Sprint:nxplatform #100, nxplatform #101, nxplatform #102
-
Story Points:1
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'
- is related to
-
NXDOC-2615 We should document the OS/JVM server timezone behavior
- Resolved
-
NXP-32056 Fix exact date query against Oracle
- Resolved