title: Date Between Query in Cosmos DB
body: <p>I am in the building a simple event store in Cosmos DB that has documents that are structured something like this:</p>
  
  <pre><code>{
      "id": "e4c2bbd0-2885-4fb5-bcca-90436f79f155",
      "entityType": "contact",
      "history": [
          {
             "startDate": 1504656000,
             "endDate": 1504656000,
             "Name": "John"
          },
          {
             "startDate": 1504828800,
             "endDate": 1504828800,
             "Name": "Jon"
          }
      ]
  }
  </code></pre>
  
  <p>This might not bet the most efficient way to store it but this is what I am starting with. But I want to be able to query all <em>contact</em> documents out of the db for a certain period of time. The <strong>startDate</strong> and <strong>endDate</strong> represent the time the record was valid. The history currently contains the entire history of the record which probably could be improved.</p>
  
  <p>I have tried creating a query like this:</p>
  
  <pre><code>SELECT c.entityType, c.id,history.Name, history.startDate  FROM c
  JOIN history in c.history
  where
      c.entityType = "contact" AND
      (history.StartDate &lt;= 1504656001
          AND history.EndDate  &gt;=  1504656001)
  </code></pre>
  
  <p>This query should return the state of the contact for 9/7/2017 but instead it is returning every one of the history. I have played with several options but I am not sure what I am missing. </p>
  
  <p>I have also tried setting the index (maybe that is the issue?) So I have included the indexing policy here:</p>
  
  <pre><code>{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
      {
          "path": "/*",
          "indexes": [
              {
                  "kind": "Range",
                  "dataType": "String",
                  "precision": -1
              },
              {
                  "kind": "Range",
                  "dataType": "Number",
                  "precision": -1
              }
          ]
      }
  ],
  "excludedPaths": []
  }
  </code></pre>
  
  <p>What am I missing? Is the index correct? Is my query correct for a date between query? </p>