Hi, I am seeing some weird results when doing a date compare that I just don’t seem to understand.
SELECT DocNum, `$MdfdTmstmp` FROM tranData WHERE DocNum IN (available_daily_docs)
$MdfdTmstmp DocNum 2022-03-11T12:30:52.5785330-05:00 61710997 2022-03-11T12:33:50.9081210-05:00 61710998 2022-03-10T13:57:03.2451250-05:00 61710989 2022-03-11T12:30:44.2181240-05:00 61710996
For the curious, this query is part of a CTE and the available_daily_docs has a bunch of DocNums. I have omitted it here to keep it concise
Here’s the same query with a between clause
SELECT DocNum, `$MdfdTmstmp` FROM tranData WHERE DocNum IN (available_daily_docs) and `$MdfdTmstmp` between "2022-03-10" and "2022-03-11"
$MdfdTmstmp DocNum 2022-03-10T13:57:03.2451250-05:00 61710989
What am I missing? The dates are in ISO 8601 format. And from what I understand, BETWEEN includes both predicates. So, it should have returned all 4 rows. But it drops everything with the 3/11 date. Why? Just trying to understand how this behaves under the covers…
Running this on CBE 6.6 on Analytics service.
I think I know what’s going on but just want to be sure… when I put a date as “2022-03-10” does it equate to “2022-03-10T00:00:00.000-05:00”? Or does it equate to 2022-03-10T00:00:00.000Z UTC? Or what exactly is going on here? I know N1QL documentation says specify the date with time and timestamp or substitute with %. I did try with “2022-03-10 %” in my query but it gave the same result.