Date comparison in Analytics Query

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.

Regards
Amrish

Hi @amrish ,

$MdfdTmstmp field is of type string, so Analytics compares them as strings.

SELECT v, v between "2022-03-10" and "2022-03-11"
FROM [
  "2022-03-11T12:30:52.5785330-05:00",
  "2022-03-11T12:33:50.9081210-05:00", 
  "2022-03-10T13:57:03.2451250-05:00",
  "2022-03-11T12:30:44.2181240-05:00"
] v

returns

[
    {
      "v": "2022-03-11T12:30:52.5785330-05:00",
      "$1": false
    },
    {
      "v": "2022-03-11T12:33:50.9081210-05:00",
      "$1": false
    },
    {
      "v": "2022-03-10T13:57:03.2451250-05:00",
      "$1": true
    },
    {
      "v": "2022-03-11T12:30:44.2181240-05:00",
      "$1": false
    }
  ]

Try using “2022-03-12” instead of “2022-03-11” in between operator:

SELECT v, v between "2022-03-10" and "2022-03-12"
FROM [
  "2022-03-11T12:30:52.5785330-05:00",
  "2022-03-11T12:33:50.9081210-05:00", 
  "2022-03-10T13:57:03.2451250-05:00",
  "2022-03-11T12:30:44.2181240-05:00"
] v

returns

[
    {
      "v": "2022-03-11T12:30:52.5785330-05:00",
      "$1": true
    },
    {
      "v": "2022-03-11T12:33:50.9081210-05:00",
      "$1": true
    },
    {
      "v": "2022-03-10T13:57:03.2451250-05:00",
      "$1": true
    },
    {
      "v": "2022-03-11T12:30:44.2181240-05:00",
      "$1": true
    }
  ]