title: CosmosDB query on date range + index
body: <p>I have a cosmos DB whose size is around 100GB. 
  I successfully create a nice partition key, i have around 4600 partition on 70M records, but I still need to query on two datetime fields that are stored as a string, not in an epoch format. </p>
  
  <p>Example json: </p>
  
  <pre><code>"someField1": "UNKNOWN",
  "someField2": "DATA",
  "endDate": 7014541201,
  "startDate": 7054864502,
  "someField3": "0",
  "someField3": "0",
  </code></pre>
  
  <p>i notice when i do <code>select * from tbl</code>  and when i do <code>select * from tbl where startDate &gt; {someDate} AND endDate&lt;{someDate1}</code> latency different is around 1s, so this filtering does not decrease my latency time. 
  Is it better to store date types as number? Does cosmos have better performance on epoch query ranges? 
  I am using SQL API. </p>
  
  <p>Also when i try to add hash indexes on a startDate and endDate he basically convert that into two indexes. 
  Example:</p>
  
  <pre><code> "path": "/startDate/?",
          "indexes": [
              {
                  "kind": "Hash",
                  "dataType": "String",
                  "precision": 3
              }
              ]
      }, 
  </code></pre>
  
  <p>this is converted to </p>
  
  <pre><code>"path": "/startDate/?",
          "indexes": [
              {
                  "kind": "Range",
                  "dataType": "Number",
                  "precision": -1
              },
              {
                  "kind": "Range",
                  "dataType": "String",
                  "precision": -1
              }
          ]
  </code></pre>
  
  <p>Is that a normal behaviour or it is related to my data? 
  Thanks.</p>
  
  <p>I checked query metrics, and for 4k records query to cosmosDB is executed in 100ms. I would like to ask you is it normal behaviour that </p>
  
  <pre><code>var option = new FeedOptions { PartitionKey = new PartitionKey(partitionKey),  MaxItemCount = -1};
  
  var query= client.CreateDocumentQuery&lt;MyModel&gt;(collectionLink, option)
              .Where(tl =&gt; tl.StartDate &gt;= DateTimeToUnixTimestamp(startDate) &amp;&amp; tl.EndDate &lt;= DateTimeToUnixTimestamp(endDate))
          .AsEnumerable().ToList();
  </code></pre>
  
  <p>this query returns 10k results (in Postman its around 9MB size) in 10-12s? This partition contains around 50k records.  </p>
  
  <blockquote>
    <p>Retrieved Document Count            :          12,356<br>
    Retrieved Document Size                  :      12,963,709 bytes<br>
    Output Document Count                    :           3,633<br>
    Output Document Size                     :       3,819,608 bytes<br>
    Index Utilization                        :           29.00 %<br>
    Total Query Execution Time               :          264.31
    milliseconds</p>
    
    <blockquote>
      <p>Query Compilation Time                 :            0.12 milliseconds</p>
      
      <p>Logical Plan Build Time              :            0.07 milliseconds</p>
      
      <p>Physical Plan Build Time             :            0.06 milliseconds</p>
      
      <p>Query Optimization Time              :            0.01 milliseconds</p>
      
      <p>Index Lookup Time                      :           51.10 milliseconds</p>
      
      <p>Document Load Time                     :          140.51 milliseconds</p>
      
      <p>Runtime Execution Times</p>
      
      <p>Query Engine Times                   :           55.61 milliseconds</p>
      
      <p>System Function Execution Time       :            0.00 milliseconds</p>
      
      <p>User-defined Function Execution Time :            0.00 milliseconds</p>
      
      <p>Document Write Time                    :           10.56 milliseconds</p>
      
      <p>Client Side Metrics </p>
    </blockquote>
    
    <p>Retry Count                            :           0  </p>
    
    <blockquote>
  <pre><code>        Request Charge                         :          904.73 RUs
  </code></pre>
    </blockquote>
  </blockquote>