title: Elasticsearch Date Aggregations
body: <p>I'm struggling to put together a query and could use some help. The documents are very simply and just record a users login time</p>
  
  <pre><code>{
  "timestamp":"2019-01-01 13:14:15",
  "username":"theuser"
  }
  </code></pre>
  
  <p>I would like counts using the following rules based on an offset from today, for example 10 days ago.</p>
  
  <ul>
  <li>Any user whose latest login is before 10 days ago is counted as 'inactive user'</li>
  <li>Any user whose first login is after 10 days ago is counted as 'new user'</li>
  <li>Any one else is just counted as 'active user'.</li>
  </ul>
  
  <p>I can get the first and latest logins per user using this (I've found this can also be done with the top_hits aggregation)</p>
  
  <pre><code>GET mytest/_search?filter_path=**.buckets
  {
      "aggs" : {
          "username_grouping" : {
              "terms" : {
                  "field" : "username"
              },
              "aggs" : {
                  "first_login" : {
                      "min": { "field" : "timestamp" }
                  },
                  "latest_login" : {
                      "max": { "field" : "timestamp" }
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p>I was thinking of using this as the source for a date range aggregation but couldn't get anything working.</p>
  
  <p>Is this possible in one query, if not can the 'inactive user' and 'new user' counts be calculated in separate queries?</p>
  
  <p>Here's some sample data, assuming todays date is 2019-08-20 and an offset of 10 days this will give counts of 1 for each type of user</p>
  
  <pre><code>PUT _template/mytest-index-template
  {
    "index_patterns": [ "mytest" ],
    "mappings": {
      "properties": {
        "timestamp": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" },
        "username": { "type": "keyword" }
      }
    }
  }
  
  POST /mytest/_bulk
  {"index":{}}
  {"timestamp":"2019-01-01 13:14:15","username":"olduser"}
  {"index":{}}
  {"timestamp":"2019-01-20 18:55:05","username":"olduser"}
  {"index":{}}
  {"timestamp":"2019-01-31 09:33:19","username":"olduser"}
  {"index":{}}
  {"timestamp":"2019-08-16 08:02:43","username":"newuser"}
  {"index":{}}
  {"timestamp":"2019-08-18 07:31:34","username":"newuser"}
  {"index":{}}
  {"timestamp":"2019-03-01 09:02:54","username":"activeuser"}
  {"index":{}}
  {"timestamp":"2019-08-14 07:34:22","username":"activeuser"}
  {"index":{}}
  {"timestamp":"2019-08-19 06:09:08","username":"activeuser"}
  </code></pre>
  
  <p>Thanks in advance.</p>